Access 2000 Comparing and joining two tables

[jonsan]

I need to compare two identical (in terms of design) tables, and then take an action depending on the comparison. To be more specific, I need to take FieldX from Table1, and look in Table2 to see if there is a record with a matching value of FieldX.
If there isn't a match, the entire record gets copied from Table1 and added to Table2.
If there is a match, specific fields get copied from Table1 and overwritten into the record in Table2.

I am familiar with SQL, and plan to write an SQL script to accomplish this task. Does Access have a built in feature that could perform this task? Any suggestions on how to build the SQL statement?

Thanks!

 

[PHV]

You have to write 2 different queries:
1) An append query
INSERT INTO Table2
SELECT * FROM Table1
WHERE Table1.FieldX Not In (SELECT FieldX FROM Table2);
Another way:
INSERT INTO Table2
SELECT Table1.* FROM Table1 LEFT JOIN Table2 ON Table1.FieldX = Table2.FieldX
WHERE Table2.FieldX Is Null;
2) An update query
UPDATE Table1 INNER JOIN Table2 ON Table1.FieldX = Table2.FieldX
SET Table1.FieldY= Table2.FieldY Table1.FieldZ= Table2.FieldZ ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244