Add or update from one table to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pete - 23 Nov 2005 11:03 GMT

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.

Reply to this Message

Marshall Barton - 23 Nov 2005 15:56 GMT

>Re Access 97
>
[quoted text clipped - 5 lines]
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Pete - 24 Nov 2005 10:28 GMT

I thought that might be the case.

Many thanks Marshall!

> >Re Access 97
> >
[quoted text clipped - 19 lines]
> ON oldtable.keyfield = newtable.keyfield
> WHERE oldtable.keyfield Is Null

Reply to this Message