Compare records in Two Tables

[Guest]

Each month I export information from another database into Access. There are
two tables involved: One containts the old data, the other is the updated
data and contains all existing records including modifications to old records
and new data. Each record has a unique field ID. In order to keep the data
clean, I need to identify which records have been modified based on the
contents of 3 fields, ProjectAddress, R_, and Company.

Suggestions?

 

[Jeff Boyce]

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>

 

[Guest]

Yes .. I have considered the other choices. We are currently using a
combination of sledgehammer and append. However, we are linking to a
Geodatabase. The records are mapped following export and appended to
existing (and already mapped) data using the ID field. Records are therefore
updated and attached to geo data based on the *original* location of the
property. If the location changes, we have to remap. Those few selected
records usually need to be remapped by hand.

I think I have solved the problem. I searched in more answers and found the
command <>[TableName].[FieldName] command. It appears to work well for
multiple fields if I drop into the "or" section for second parameter and
returns any location-type records that may require individual attention.

Thanks so much for your help!

 

[AnExpertNovice]

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.