Updating Numeric Values

[dssrun]

I have two records that are brought in from an outside source. However, in my database I need their fees and payments to be combined because, now, they are one entity in our system, but the system I am getting them from does not know that. So i have tried multiple update queries that aren't working. Any suggestions would be appreciated. Thanks in advanced

[weekend00]

didn't get you. what do you want to do?

[dssrun]

Let me know if this helps, here is an example of what I mean (simplified):

An outside table is imported into my access table with this information, and I can't modify the information outside of access:

FacilityA $0 $10
FacilityB $5 $0

In reality our program combined FacilityA and FacilityB, but it is still separated in the outside table (can't be changed in that outside table). What I need is an update query (or another query) to tell my access table to update FacilityB to FacilityA values, to give me:

FacilityB $5 $10

I need this done because other tables that join with this have FacilityB, but do not have FacilityA. Simply changing the name of FacilityA to B will not do it either because it brings in other values from other tables twice that I do not need.

[weekend00]

You example is very helpful.
I assume you table has this fields:
ID Facility Fee payment
11 FacilityA $0 $10
11 FacilityB $5 $0
23 FacilityA $0 $11
23 FacilityB $6 $0

there should be a pair of records for each ID, is it right?

the update query:

update tbl inner join tbl as tbl1 on tbl.id=tbl1.id set tbl.payment=tbl1.payment where tbl.facility="FacilityB" and tbl1.facility="FacilityA"

after checking, you can remove the records with "FacilityA"
delete * from tbl where facility="FacilityA"

[dssrun]

I will try this out but my table is setup like this, but there are not pairs

Code Facility Date Fee Payment

A FacilityA 10/1 $0 $10
B FacilityB 10/1 $5 $0

[dssrun]

Actually I have two Primary keys, The Facility and Date

[weekend00]

did you mean there are always 2 records for each day? if so, the date is the id.

[dssrun]

this external data is appended each month to the table, so the next month will read:

FacilityA 11/1/2010
FacilityB 11/1/2010

So yes i didn't even think about it, but my ID can be the date

[dssrun]

It is asking me to enter a parameter value for tbl1 when I try to run the query

[dssrun]

Fixed the problem when I switched to design mode...thanks for the help