Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Comparing Data Differences Between Two Identical Access Tables

    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!

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Rui
    -------
    R4

  3. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No, I'm sorry, it is not a regular outer join query that I need. It is based on data changes at the field level and not the record level. For example, in tblDataYesterday a field called PartNumber may be 1234 and in tblDataToday the field PartNumber has been changed to 5678. I want to find that change and display it.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    KG,

    What is your Primary Key in the two tables? Are they the same value? If so you need a 1 to 1 join and then a query with a line for each field you want to compare which checks to see if the two field values are the same. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    12,519
    Thanks
    152
    Thanked 1,398 Times in 1,221 Posts
    Quote Originally Posted by Kansasgirl1 View Post
    No, I'm sorry, it is not a regular outer join query that I need. It is based on data changes at the field level and not the record level. For example, in tblDataYesterday a field called PartNumber may be 1234 and in tblDataToday the field PartNumber has been changed to 5678. I want to find that change and display it.
    I am sorry, the example based on a join, in the article, uses an inner join, not an outer join and it was meant to provide a general approach to a solution for your problem.

    RetiredGeek also gave you another approach. If the number of fields is small, you could do this manually. If you have a large number of fields, probably you could just build a quick VBA procedure to generate the SQL dynamically for all your fields.
    Rui
    -------
    R4

  6. #6
    New Lounger kalvinson's Avatar
    Join Date
    Dec 2013
    Location
    The beautiful and serene Yorkshire Dales, England, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There is quite a simple way to do this, but you need to be running Office 2013 Professional to do so. Click on Office 2013 on the Start Menu and it will show a folder called Office 2013 Tools. Click on that folder and you will see a shortcut called Database Compare 2013. Click on that and it will give you a choice of which parts of the database that you want to compare. Select the path to each database and run the program. It will show you the differences between your selection.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by kalvinson View Post
    There is quite a simple way to do this, but you need to be running Office 2013 Professional to do so. Click on Office 2013 on the Start Menu and it will show a folder called Office 2013 Tools. Click on that folder and you will see a shortcut called Database Compare 2013. Click on that and it will give you a choice of which parts of the database that you want to compare. Select the path to each database and run the program. It will show you the differences between your selection.
    Unfortunately that approach won't help you with your task - see Basic tasks in Database Compare... as it only looks at design differences. Quoteing the article "Database Compare doesn't look for changes in the data stored in tables, but by exporting tables or query results to Excel workbooks, you can compare those two workbooks in Spreadsheet Compare. Learn how to Export Access data to see record updates." Unfortunately you would need Excel 2013 to take that approach.
    Wendell

  8. #8
    New Lounger kalvinson's Avatar
    Join Date
    Dec 2013
    Location
    The beautiful and serene Yorkshire Dales, England, UK
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I stand corrected ref. database compare. However, with Office 2013 Pro it is still quite simple to export the two tables from Access to Excel, run spreadsheet compare, and that will show the differences in the data sets.

  9. #9
    New Lounger
    Join Date
    Dec 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I really appreciate the responses to this, but I can't export to Excel. I am familiar with track changes in Excel. There has got to be a way to do this in Access. Any other thoughts?

  10. #10
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    It seems to me you have 3 ways of tackling this
    (1) Use Geek's suggestion to use a query with a join on the common PK and have 100 or conditions a.Field(n)<> b.Field(n) to locate records with different field contents (if a query can handle that many otherwise a series of queries). And an outer join to show new and deleted records.
    (2) Maintain a log of changes as they are made. (This does not help with historical data.)
    (3) Use VBA to loop through recordsets comaring field values, the option I'd personally use.

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Well, it's not a trivial task with 100 fields, but it can be done. The first thing you have to do is join the two tables on a unique identifier so you know that you are comparing the right ones. If you don't have a unique identifier, then there isn't any good way to do it. Which is why both RG and Rui made their comments. And it's an inner join. You don't want to compare records which only exist in one table or the other, although you may want to explore the Today table for new records.

    Once you have that query, then you run a second query against that specifying that each column in Yesterday is the same as today, and that gives you the records that have not changed. Then you run a query as an outer join agains the query that returned the set that hasn't changed and the original set of data where the equal set doesn't contain records that were in the first query.

    However if you 100 fields in your table, that's going to be three pretty complex queries - and you may find issues with the query designer, or the limit on SQL statments length in Access, and performance is likely to be ugly. You don't indicate how many records you have, but it will probably take a good deal of memory to run the queries as well. An alternative if you are proficient with DAO and VBA is to create a procedure which opens the two tables and does a field by field compare to identify those records where there are differences. How you indicate that might involve a third table where you store the unique identifier.

    The most common reason we see this kind of need is to identify records that have changed, and the easiest way is to put a time and date field in each record that records the last time a record was changed. You don't indicate the reason for wanting to identify the records that are different, but if it to simply to identify the records that have changed that does the deed. With other details such as where the data comes from (external system, Access data entry, imports of Excel, etc.) we may be able to make more specific suggestions.
    Wendell

  12. The Following User Says Thank You to WendellB For This Useful Post:

    ruirib (2013-12-13)

  13. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I prepared this database a while ago, and it assumes that you have setup the tables and their primary keys in the table provided and that the databases have the same name.
    Good luck, ask questions if you have trouble
    Attached Files Attached Files

  14. #13
    New Lounger
    Join Date
    Dec 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your response. I have downloaded the zipped file but I don't have the backend database to look at the table data. Can you provide that to me?

  15. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Those BE tables are linked to on the fly, as long as you have specified the paths and correct database name and populated the PK table it should all work

  16. #15
    New Lounger
    Join Date
    Dec 2013
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I do not understand the statement, populating the PK table. I have created a database with the same name and tried to link the tables to it, but it is not working. Where does Table Member and PKField id_member come from?
    Attached Images Attached Images

Page 1 of 2 12 LastLast

Posting Permissions

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