Need help with a "Not in" query

[klange]

I need a query to do the following:
I have a table I get from my headquarters that has cumulative data in it. I copy the data to another table that I maintain locally with extra fields and records that hold locally significant data. I need a query that will bring over new records from the headquarters table that don't already exist in the local table, and leave the existing records in the local table untouched. The field used to Identify a record is 'Sequence_ID', Locally generated records in the local table will not have one, so this cannot be a key field in the destination table.
Can someone help me with this?

 

[scriverb]

I am assuming from your explanation that each record being brought in will have a Sequence_ID that is unique to that individual record. There will be records in this table(tblHdQrts) that already have been appended to the local table(tblLocal). There will be new records in this table that need to be added to the local table. Both tables have a field called Sequence_ID that is derived from the tblHdQrts when the records are added but the locally generated records this field left blank. Is this the case? I will assume that it is.

So, you only want to APPEND records to the local table if the Sequence_ID does not exist in the local table. This can be accomplished with a Left Join in an APPEND query.

INSERT INTO tblLocal ( Sequence_ID, Field1, Field2, Field3 )
SELECT tblHdQrts.Sequence_ID, tblHdQrts.Field1, tblHdQrts.Field2, tblHdQrts.Field3
FROM tblHdQrts LEFT JOIN tblLocal ON tblHdQrts.Sequence_ID = tblLocal.Sequence_ID
WHERE (((tblLocal.Sequence_ID) Is Null));

This query makes a comparison on Sequence_ID between the table from headquarters and the local table and only appends records if the field in the local table is null or non-existent. This identifies records not yet added.

Now the table names and the references to Field1, Field2, and Field3 will have to be updated to reflect the fields from your tables.

Let me know if this is getting close to what you want. Bob Scriver

 

[klange]

Your "read back" of my problem sounds as if you understand it exactly, which is suprising because it's often hard to describe something this complicated in an email. I won't be able to impliment it until Monday. I will let you know how it works. Thanks for your quick response.