Compare Records in Identical Tables and Highlight Changes (Access 2007)

What is the best way to take two identical tables, one consisting of account specific records only, and another consisting of records from all accounts (the redundancy is a security implementation), and easily examine which records have been added, and which ones have been modified when comparing to the master table?

I want to be able to manually approve both individual and batch appends/updates (adding new records from the account specific table to the master table, or updating master table records to reflect changes in the account specific table) using this method, and preferably have a means, through a form/query combination or other means, of using conditional formatting to determine which specific fields have been modified in some instances, and entire records in others.

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can build a query something like this:

SELECT A.*, B.* FROM YourMasterTable A JOIN YourOtherTable B ON A.someField = B.SameField WHERE A.Field1 <> B.Field1 OR A.Field2 <> B.Field2 etc etc

However, this query will most likely not be editable, nor will you be able to tell which of the fields are different between the tables. I'd also suggest that if you are truly clueless as to the concept of master/child relationships, and if you are indeed trying to work with tables related as such, that you would be well advised to increase you knowledge of this concept before you attempt to do this. As I mentioned in earlier posts, there are third party utilities that do this (one is Total Access Detective from, which always indicates that the process is anything but trivial ... not trying to dissuade you from trying this, just advising you to tread carefully when working this data in this manner.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The simplest way is through a third party utility, but I suspect you don't want that.

You could build a master/child setup, where your child records would navigate through as you move through your Parent records. You could then do a field-by-field comparison (assuming the tables are IDENTICAL in structure) something like this:

If Me.Field1 <> Me.YourSubform.Form.Field1 Then
  Me.YourSubform.Form.Field1.Backcolor = vbRed
  Me.YourSubform.Form.Field1.Backcolor = vbWhite
End If

Note that YourSubform is the name of the Subform CONTROL on the parent form, and may or may not be named the same as the form you're using as a subform.
drtrmillerAuthor Commented:
I am clueless as to the nature of master/child.  Are you saying I create a form where there is the "master" data and a subform containing "child" data?  I am thinking I have many "children," so I just don't know how to make this work.  

I was thinking I needed a query that could just show new/modified records and then I could handle the conditional statements through that form format, but how do I get a query to show new/modified records from Account A, B, and C (each with their separate table) to compare to a master account list (single table containing all records from A+B+C...)?
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.