Query that compares two tables

[hainejo]

I have a two tables that I would like to compare to identify changes. I import data ecery morning into a table. I would like to compare todays data with yesterdays data and identify what records changed. Both tables are identical in design. Can anyone provide the SQL statement(s) to accomplish this?

 

[bat17]

If you use the Unmatched Query Wizard it will create a query for you that will show the records that have changed.


HTH

Peter

 

[foxhound]

Peter, your suggestion works great if there is one record that is not in the other. However, what if you want to compare both record for record and field for field in table?

Foxhound

 

[bat17]

I was forgetting that the Wizard only works with one field
If you go into design view after using the wizard you will see that it has created a Left Join on the field you picked. If you drag and join all of the other fields that you want to test as well and set them to Left Joins, by right clicking and setting the Join Properties, then it should pull out all of the records that have changed in any of those fields.

HTH

Peter

 

[foxhound]

Peter, thanks for the follow up response. I will give it a try a little later

 

[fishingmachine]

Hi Peter, I am trying to do the same thing as foxhound and i used your suggestion of creating additional joins on all of the fields in both tables.

i am experiencing one problem. null values in both tables for the same field show up as a difference.

any suggestions on how to make a null value = a null value in a find unmatched query?

thanks,
fishingmachine

 

[tonnic]

Hi,

Null should always be Null, normally ...
Are you sure, that both fields have the same properties? It could be that there is a "space" in the field, and then the field has a value and is not null.