Add or update from one table to another

[Guest]

Re Access 97

I have a huge lookup table which I want to overwrite with newer data from
another table if the same records exist in both tables

If not, then add the record as a new record to the lookup table.

I want to automate the whole process. Will I require both an update and an
append query?

Any comments as to how to achieve this would be greatly appreciated.

Many thanks.

 

[Marshall Barton]

Re Access 97

I have a huge lookup table which I want to overwrite with newer data from
another table if the same records exist in both tables

If not, then add the record as a new record to the lookup table.

I want to automate the whole process. Will I require both an update and an
append query?

Yes you need to do this in two queries.

UPDATE oldtable INNER JOIN newtable
ON oldtable.keyfield = newtable.keyfield
SET oldtable.fieldA = newtable.fieldA,
oldtable.fieldB = newtable.fieldB,
. . .

INSERT INTO oldtable
SELECT newtable.*
FROM newtable LEFT JOIN oldtable
ON oldtable.keyfield = newtable.keyfield
WHERE oldtable.keyfield Is Null

 

[Guest]

I thought that might be the case.

Many thanks Marshall!