Kate

Is your task to identify changed records, or to ensure that you have a
"current" set of data?

If the latter, could you simply run an update query?  That might be a bit of
a sledgehammer approach, but you wouldn't need to be bothered with first
identify which/what had changed.

And if you have new records to add, an append query would do the job.
Again, if your table is indexed, no duplicates, on the recordID, only the
new recordIDs would "stick" ... the others would be rejected as duplicates.

What am I missing?

Regards

Jeff Boyce
<Office/Access MVP>

Here is an example of what I do.  These 3 queries will show you added
records, deleted records, and changed records.  To be fancier, change it
from 3 queries to 7 queries where each of the non-union queries are only
checking one field.

To begin.  I created "tblOld" 4 fields.  Mkey, ProjectAddress, R_, and
Company.
Mkey was made a long and the folloing record was added.
1   Testing   Testing   Testing   Testing
This record was used to make 4 additional records with the MKey being
changed to 2, 3, 4, & 5.

TblOld was then copied.

In tbl New, record 3 was deleted.
In tbl New, record 1 was deleted.  Record 2 had the company value changed.
Record 4 had R_ changed.

The result looks like this.
 qryChanges OldKey OldProjectAddress OldCompany OldR_ NewKey
NewProjectAddress NewCompany NewR_

    1 Testing Testing Testing
     2 Testing Changed Testing 2 Testing Testing Testing
     3 Testing Testing Testing

     4 Testing Testing Changed 4 Testing Testing Testing

From this, you can see that  record 1 was added and record 3 deleted.
You can, with a little effort, see the fields that were changed fields in
records 2 and 4.
Record 5 is not listed since it was unaffected.

The queries
AddedEntries:
SELECT tblOld.Mkey AS OldKey,
              tblOld.ProjectAddress AS OldProjectAddress,
              tblOld.Company AS OldCompany,
              tblOld.R_ AS OldR_,
              tblNew.Mkey AS NewKey,
              tblNew.ProjectAddress AS NewProjectAddress,
              tblNew.Company AS NewCompany,
              tblNew.R_ AS NewR_
FROM tblNew LEFT JOIN tblOld
ON tblNew.Mkey = tblOld.Mkey
WHERE ((([tblNew]![ProjectAddress]<>[tblOld]![ProjectAddress] Or
[tblNew]![Company]<>[tblOld]![Company] Or [tblNew]![R_]<>[tblOld]![R_]) Is
Null Or ([tblNew]![ProjectAddress]<>[tblOld]![ProjectAddress] Or
[tblNew]![Company]<>[tblOld]![Company] Or
[tblNew]![R_]<>[tblOld]![R_])=-1));

(NOTE:  The where clause is one query statement comparing each of the 3
fields and looking for a Null (added or deleted records) or -1 (changed
records)

DeletedEntries
EXACTLY the same as AddedEntries, but change LEFT JOIN to RIGHT JOIN.

qryChanges
SELECT OldKey, OldProjectAddress, OldCompany, OldR_, NewKey,
NewProjectAddress, NewCompany, NewR_
FROM AddedEntries
UNION SELECT OldKey, OldProjectAddress, OldCompany, OldR_, NewKey,
NewProjectAddress, NewCompany, NewR_
FROM DeletedEntries;

Do me favor.  Post your method if it is better.  I'm a rank amatuer and want
to learn better methods.  Mine seems rather cludgy.