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

Posted on 2009-05-01
Last Modified: 2013-11-28
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.

Question by:drtrmiller
    LVL 84
    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.

    Author Comment

    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...)?
    LVL 84

    Accepted Solution

    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.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    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

    12 Experts available now in Live!

    Get 1:1 Help Now