Add/Update Records Query

[Wiz0fBaud]

Hey Guys,
I have two tables that I want to compare based on a single field. Both tables are exactly the same except for 3 columns that were added in table B.

So basically I want to take table A and say if Field1 in table A matches field1 in table B, ok but check the rest of the columns to make sure they match.

If field 1 in table A doesn't match anything in table B, add the record to table B with null values in for the 3 columns that are different.

Does anyone have any suggestions on the best way to do this? Thanks so much for any help, it is greatly appreciated.

 

[BNPMike]

Can you clarify what is happenning? It seems you have two tables
Table A
Fields A B C D

Table B
Fields A B C D E F G

If you try to load A records into B then, if you are matching on a key field, duplicates will be rejected automatically, otherwise a new record will be created with null values in E,F,G (unless you have different settings).

So that looks straightforward but what does this mean: ok but check the rest of the columns to make sure they match.?

 

[Wiz0fBaud]

What I mean is that I will be matching on a key field like you said. But what I would like the query to do is say. Ok, I found this key field (lets call it field A) in both Tables A and B. But since table A is the master and most important table, overwrite fields BCD in table B so they match table A. If key field does not exist, create from table A and enter null values in EFG like you said...

Does that clarify it a little better?

 

[BNPMike]

So adding new records is sorted.

To update, join from A to B. This will connect all the matching records. Now in your update query design screen, set B.B = A.B and B.C = A.C and B.D = A.D. That's all you need to do, I think.