Compare 2 tables, same structure, list changes

Posted on 2012-09-14
Last Modified: 2012-09-16
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.
Question by:pdvsa
    LVL 30

    Expert Comment

    LVL 44

    Accepted Solution

    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.DOB <> T.DOB OR
    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.

    Author Closing Comment

    thank you

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now