none
Comparing Data Differences Between Two Identical Access Tables RRS feed

  • Question

  • I have two identical Access tables, for example tblDataYesterday and tblDataToday.  The data from tblDataToday has updated data changes in it that are different from tblDataYesterday.  I have a maximum of 100 fields to review.  I want to compare the two tables and display in a query the differences between the data from both tables.  Is this possible?  Any help is appreciated.  Thank you!

    Alicia Hunsberger

    Friday, December 13, 2013 9:45 PM

Answers

  • Hi Alicia,

    > I have two identical Access tables, for example tblDataYesterday and tblDataToday.

    >  I want to compare the two tables and display in a query the differences between the data from both tables.  Is this possible?

    There are three kinds of differences:

    1

    New records in tblDataToday

    2

    New records in tblDataYesterday or lost records in tblDataToday

    3

    Changed records that are both present in tblDataToday and tblDataYesterday

     

    > I have a maximum of 100 fields to review

    100 fields is quite a lot. In order not to enumerate all of your fields in a query I propose to do the following.

     

    1.

    To browse new records in tblDataToday:

    SELECT tblDataToday.*

    FROM tblDataToday LEFT JOIN tblDataYesterday ON tblDataToday.ID = tblDataYesterday.ID

    WHERE (((tblDataYesterday.ID) Is Null))

     

    2

    To browse new records in tblDataYesterday or lost records in tblDataToday:

    SELECT tblDataYesterday.*

    FROM tblDataToday RIGHT JOIN tblDataYesterday ON tblDataToday.ID = tblDataYesterday.ID

    WHERE (((tblDataToday.ID) Is Null))

     

    3

    To browse changed records simultaneously in tblDataToday tblDataYesterday:

    3.1 Create a query qChangedData_u:

    SELECT tblDataToday.*,"tblDataToday" as  ChangedIn

    FROM tblDataToday

    UNION ALL SELECT tblDataYesterday.*, "tblDataYesterday" as ChangedIn

    FROM tblDataYesterday

     

    3.2 Create a query qChangedIDs:

    SELECT All_IDs.ID

    FROM (SELECT DISTINCT * FROM (SELECT tblDataToday.* FROM tblDataToday union all SELECT tblDataYesterday.* FROM tblDataYesterday  )  AS uAll)  AS All_IDs

    GROUP BY All_IDs.ID

    HAVING (((Count(All_IDs.ID))=2));

     

    3.3 Create a query qChangedData:

    SELECT qChangedData_u.*

    FROM qChangedIDs INNER JOIN qChangedData_u ON qChangedIDs.ID = qChangedData_u.ID

    ORDER BY qChangedData_u.ID, qChangedData_u.ChangedIn

     

    So, query qChangedData allows you to compare the differences in existing records.

     

    Hope it's what you were asking for.

    Sergiy Vakshul




    Sunday, December 15, 2013 10:14 AM
  • This question is also being addressed here.

    Ken Sheridan, Stafford, England

    Tuesday, December 17, 2013 6:27 PM
  • If you can join the tables on the primary keys (assuming these are the same in both tables) then you can use criteria like this:

    SELECT tblDataYesterday.LastName,  
    tblDataToday.LastName, 
    tblDataYesterday.FirstName,  
    tblDataToday.FirstName
    FROM tblDataYesterday INNER JOIN tblDataToday 
    ON tblDataYesterday.myID = tblDataToday.myID 
    WHERE tblDataYesterday.LastName <>  tblDataToday.LastName
    OR tblDataYesterday.FirstName <>  tblDataToday.FirstName


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, December 13, 2013 10:22 PM

All replies

  • If you can join the tables on the primary keys (assuming these are the same in both tables) then you can use criteria like this:

    SELECT tblDataYesterday.LastName,  
    tblDataToday.LastName, 
    tblDataYesterday.FirstName,  
    tblDataToday.FirstName
    FROM tblDataYesterday INNER JOIN tblDataToday 
    ON tblDataYesterday.myID = tblDataToday.myID 
    WHERE tblDataYesterday.LastName <>  tblDataToday.LastName
    OR tblDataYesterday.FirstName <>  tblDataToday.FirstName


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Friday, December 13, 2013 10:22 PM
  • Create > Query Wizard> Find Duplicates Query Wizard . . . select the Table and Select the Fields . . . . Finish

    OR

    Create > Query Wizard> Find Unmatched Query Wizard . . . select the Table and Select the Fields . . . . Finish


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Saturday, December 14, 2013 4:20 PM
  • Hi Alicia,

    > I have two identical Access tables, for example tblDataYesterday and tblDataToday.

    >  I want to compare the two tables and display in a query the differences between the data from both tables.  Is this possible?

    There are three kinds of differences:

    1

    New records in tblDataToday

    2

    New records in tblDataYesterday or lost records in tblDataToday

    3

    Changed records that are both present in tblDataToday and tblDataYesterday

     

    > I have a maximum of 100 fields to review

    100 fields is quite a lot. In order not to enumerate all of your fields in a query I propose to do the following.

     

    1.

    To browse new records in tblDataToday:

    SELECT tblDataToday.*

    FROM tblDataToday LEFT JOIN tblDataYesterday ON tblDataToday.ID = tblDataYesterday.ID

    WHERE (((tblDataYesterday.ID) Is Null))

     

    2

    To browse new records in tblDataYesterday or lost records in tblDataToday:

    SELECT tblDataYesterday.*

    FROM tblDataToday RIGHT JOIN tblDataYesterday ON tblDataToday.ID = tblDataYesterday.ID

    WHERE (((tblDataToday.ID) Is Null))

     

    3

    To browse changed records simultaneously in tblDataToday tblDataYesterday:

    3.1 Create a query qChangedData_u:

    SELECT tblDataToday.*,"tblDataToday" as  ChangedIn

    FROM tblDataToday

    UNION ALL SELECT tblDataYesterday.*, "tblDataYesterday" as ChangedIn

    FROM tblDataYesterday

     

    3.2 Create a query qChangedIDs:

    SELECT All_IDs.ID

    FROM (SELECT DISTINCT * FROM (SELECT tblDataToday.* FROM tblDataToday union all SELECT tblDataYesterday.* FROM tblDataYesterday  )  AS uAll)  AS All_IDs

    GROUP BY All_IDs.ID

    HAVING (((Count(All_IDs.ID))=2));

     

    3.3 Create a query qChangedData:

    SELECT qChangedData_u.*

    FROM qChangedIDs INNER JOIN qChangedData_u ON qChangedIDs.ID = qChangedData_u.ID

    ORDER BY qChangedData_u.ID, qChangedData_u.ChangedIn

     

    So, query qChangedData allows you to compare the differences in existing records.

     

    Hope it's what you were asking for.

    Sergiy Vakshul




    Sunday, December 15, 2013 10:14 AM
  • OK, this is great, but what if I only want to display the fields that have changed?  For example, say the last name changed but not the first name?

    Alicia Hunsberger

    Monday, December 16, 2013 4:33 PM
  • Thank you for your assistance, your SQL statements work great, but I only want to show the values that have changed, not the entire record.  My tables do have a primary keys that I will be linking on. Ultimately I want to display the differences and append those changes to a history table of changed data.

    Alicia Hunsberger

    Monday, December 16, 2013 4:58 PM
  • Well, with 100 fields/columns, you've got to think about how to display or capture the differences.
    One of two ways: Positional or captioned.

    I suggest the latter, especially if the differences are not numerous....in terms of column values.
    1) Create a query to join the two tables. Remember the Column Count (COLCNT).
    2) Create a separate reporting table to store the differences: 3 columns, 1 named DiffColumn, the other DiffData1, DiffData2, each are Text column types.
    3) Open up the query with DAO OpenRecordset
    4) Use Comparison logic to detect differences : oRST1.Fields(1).Value <> oRST1.Fields(1+COLCNT).Value
    Do this for each Field in a For/Next loop
    5) AddNew to the Difference Table:
    oRSTDiff!DiffColumn=oRST1.Fields(1).Name
    oRSTDiff!DiffData1 = CStr(oRST1.Fields(1).Value)
    oRSTDiff!DiffData2 = CStr(oRST1.Fields(1+COLCNT).Value)

    Each and every changed column will be in the 3rd table.
    To append those changes to a history table, create another table with the same structure as the data tables and do an Addnew to that table when you do an AddNew to the difference/reporting table.
    Now you have exactly what you want: A difference report and a difference table.
    • Edited by Syswizard Monday, December 16, 2013 5:13 PM
    • Proposed as answer by Syswizard Monday, December 16, 2013 5:43 PM
    Monday, December 16, 2013 5:08 PM
  • Hi,

    your question was:

    > I want to compare the two tables and display in a query the differences between the data from both tables.  Is this possible?

    I thought you were having difficulties with SQL statements. So I anwered.

    Now you are talking about a history table. It's something else. I am affraid  I won't be able to help you unless you are certain what you would like to get. Sorry.

    Sergiy Vakshul



    Monday, December 16, 2013 7:04 PM
  • I'm sorry, I wasn't clear earlier.  Yes, I do want to display the differences in a query between the two tables and only the results.

    So in the query the Last Name changed only and I want to display that but not the first name because it didn't change.


    Alicia Hunsberger

    Tuesday, December 17, 2013 2:26 AM
  • There will be approximately 30 fields in the table. I'm sorry but I am not adept at writing code in Access VBA.

    Alicia Hunsberger

    Tuesday, December 17, 2013 2:29 AM
  • There will be approximately 30 fields in the table. I'm sorry but I am not adept at writing code in Access VBA.

    Alicia Hunsberger

    So getting back to the table differences....are you now indicating both tables will always have the same number records and they will have matching keys ?

    Tuesday, December 17, 2013 6:08 PM
  • This question is also being addressed here.

    Ken Sheridan, Stafford, England

    Tuesday, December 17, 2013 6:27 PM