Hi- this is my first post here, and I'm new to using Access (2003), I hope someone can help.

I get massive data files that I load into a proprietary system and then updated files that have extra records or deleted records that I can easily find. However, there are over one hundred fifty fields and tens of thousands of records and I want to identify only the records that have changes in them.

Is there any way to identify the only records that have at least one change in them so I can work with only these records to make the updates?

Any help would be appreciated.

J

May I assume that you "updated files" are loaded into *separate* tables in the Access DB?

And may I assume that you have some way of knowing which records in the existing table correspond to which records in the new table (e.g, some sort of unique identifier)?

If so, it's not hard, but it's quite tedious.

You will need to do something like this:

SELECT T1.id
FROM oldTable AS T1, newTable AS T2
WHERE T1.id = T2.id
AND ( T1.field1 <> T2.field1
      OR T1.field2 <> T2.field2
      OR T1.field3 <> T2.field3
      ...
      OR T1.field150 <> T2.field150 )

That code assumes that the "id" field is the one unique field (could be a combination of fields) that "connects" the two tables.

This will give you a list of the the id's of all the records that are not the same. Now, finding in what WAY the pairs of records are not the same is much more tedious, and pretty much has to be done on a per-field basis. It's a job that is better done in VBA or some program external to Access.

Thanks Old Pedant. You are correct that I get a new file and import it as a new table and there is a key field. I tried your query and it gave me exactly what I was looking for, ID where any one of the values in any one of the fields were different.

Thanks for helping me save time!

J