Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010

    Unanswered: How to compare data between 2 tables

    Hi guys,

    I am new to access, i am using access 2003 and this is my first post in dBforums.

    I am having 2 tables and they are identicle fields. The current table called dbo_LINE_LIST, and the old table called dbo_PREV_LINE_LIST.

    I want to compare the old table against current table and if there are any changes to the current table, access will return all fields in the table and the changed data will show with yellow highlight. I know access can do it but I don't know how to. Thank you very much for your help.


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    There are two different features in your question:

    1) Retrieving lines from 2 structurally identical tables where data in any column in one table differs from data in the corresponding column of the other table.
    If the tables have a primary key or at least an identity column, the SQL statements are rather simple:
    a) Intermediate Query:
    SELECT <Table1>.<Identity>
    FROM <Table1>
    LEFT JOIN <Table2> ON <Table1>.<Column1> = <Table2>.<Column1>
    WHERE <Table2>.<Column1> Is Null
    SELECT <Table1>.<Identity>
    FROM <Table1>
    LEFT JOIN <Table2> ON <Table1>.<Column2> = <Table2>.<Column2>
    WHERE <Table2>.<Column2> Is Null
    SELECT ...
    SELECT <Table1>.<Identity>
    FROM <Table1>
    LEFT JOIN <Table2> ON <Table1>.<ColumnX> = <Table2>.<ColumnX>
    WHERE <Table2>.<ColumnX> Is Null
    b) Final query:
    SELECT *
    FROM <Table1>
    WHERE <Table1>.<Identity>
    NOT IN ( 
    SELECT <Identity>
    FROM <Intermediate Query>
    You need to use two queries because Access does not permit to perform this kind of combined operations in a single query.

    The catch being that running this kind of query can be very slow if there are many lines and/or if there are many columns to compare. However, if every column implied in the comparisons is indexed the method is satisfactorily fast.

    2) Changing the visual aspect of the data when a condition is met. It depends on how the data is displayed. In a form or a report you can use bits of VBA to modify the property of the textbox controls linked to the data if certain conditions are met (or if they are unmet). with a form in datasheet view, you'll use the Conditional Format features present in Access.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts