Compare 2 tables, same structure, list changes

Experts, I have 2 tables with the same structure but not the same data.  I want to compare these 2 tables to see what is different between them.   I am importing data from excel and made a temp table before Inserting the new data.   I am comparing the temp table with the newer table of imported data.  I am not sure if there is some simple way to do this i/o writing a query comparing the 2 tables.  

I am using 2007.
pdvsaProject financeAsked:
Who is Participating?
This will usually require three queries, which may be combined in a single Union query or run separately.

The first two will tell you which rows are not found in the other table, based on the key column(s). For this, you construct Left Join and Right Join queries on the key fields and check for a Null value in the 'other' table.  You only need to report the key column(s) values.  If you are constructing a union query, you would need all the fields as well as a new column, indicating the type of status ("unmatched in Temp table", "not found in production table")

The third query is an Inner Join with key column(s) equal.  I usually alias my table names to reduce the clutter. ("As T" and "As P" as an example of aliases for Temp and Production tables)  If you just wanted to identify the key columns, then you only need to have those in the Select clause.  However, you can save yourself some time by selecting all the non-key columns.
I go to the SQL view and copy the non-key columns to either Word or Excel.  In those applications, I construct the Where clause conditions.

P.LastName <> T.LastName OR
P.FirstName <> T.FirstName OR
P.MiddleName <> T.MiddleName OR
P.Salary <> T.Salary

I transfer the Where clause conditions back into Access.

Note: you can also write VBA code that will construct the Where conditions string by iterating the fields of one of the tables.
pdvsaProject financeAuthor Commented:
thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.