Comparing Data Differences Between Two Identical Access Tables

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. The are joined together with a primary key of myID.  I have a maximum of 30 fields to review.  I want to compare the two tables and display in a query the data values that have changed between the two tables. 

For example, the tables have myID, FirstName, LastName, etc.

The last name value yesterday was Smith, today it is Jones, the first name has not changed.  I want to display ONLY the changed data which is the LastName.

Is this possible with SQL queries?  Or will I have to write this in VBA?  I am not very adept a writing in VBA.  Any help is appreciated.  Thank you!

Answer 1

Hi,

tell me if is this that you want to display :

SELECT Table1.myId, IIf([Table2].[WD]<>[Table1].[WD],[Table2].[WD],"") AS WD, IIf([Table2].[RepNum]<>[Table1].[RepNum],[Table2].[Repnum]) AS RepNum

FROM Table1 INNER JOIN Table2 ON Table1.myId = Table2.myId;

Bye Mimmo

Answer 2

I've created the function and the query. 

 When I run the query it goes into debug mode

and stops on this line in the Function:

 If CurrentVals(n) <> rst.Fields(n) Then

I also noticed that your SQL statement has contactID in it as well, so both of my tables have the following fields:

MyID, ContactID, FirstName, LastName

MyID and ContactID are number data types, and the other two are text of course.

Answer 3

Essentially, you'd  just need to append the new data from its source, whatever that may be, and in the 'append' query include a column which has the date value to be inserted into a column which identifies each subset of rows.  Say for instance you are importing data from somewhere, do so into a  into a pre-defined 'holding' table which you empty first with:

DELETE *

FROM HoldingTable;

Having imported the new data into this table you can then append from it into your single operational table, which will include an additional column of date/time data type; let's call it TransactionDate.  For simplicity in this example I'll again restrict the data to FirstName and LastName columns.  Let's say you want to insert the current date as TransactionDate when you append the data, the query would be like this:

INSERT INTO OperationalTable(TransactionDate, FirstName, LastName)

SELECT DATE(), FirstName, LastName

FROM HoldingTable;

Or you might want to enter a date value when you execute the query:

PARAMETERS [Enter transaction date:] DATETIME;

INSERT INTO OperationalTable(TransactionDate, FirstName, LastName)

SELECT [Enter transaction date:], FirstName, LastName

FROM HoldingTable;

You can compare the rows from one date to another by amending my function a little:

Public Function CompareVals(lngID As Long, dtmTransactionDate As Date, ParamArray CurrentVals() As Variant) As String

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim n As Integer

    Dim var As Variant

    strSQL = "SELECT FirstName,LastName FROM OperationalTable " & _

        "WHERE MyID = " & lngID & " AND TransactionDate = #" &_

        Format(dtmTransactionDate,"yyyy-mm-dd") & "#"

    Set rst = CurrentDb.OpenRecordset(strSQL)

    For n = 0 To 1

        If CurrentVals(n) <> rst.Fields(n) Then

            CompareVals = CompareVals & ", " & rst.Fields(n).Name & ":" & CurrentVals(n)

        End If

    Next n

    CompareVals = Mid(CompareVals, 3)

End Function

When calling the function in a query you'd then have parameters for the two dates to be compared, e.g. today's and yesterday's:

PARAMETERS [Today's date:] DATETIME,

[Yesterday's date:] DATETIME;

SELECT TransactionDate, MyID,

CompareVals(MyID,[Yesterday's date:],FirstName,LastName) AS Changes

FROM OperationalTable

WHERE TransactionDate = [Today's date:]

AND LEN(CompareVals(ContactID,Firstname,LastName)) > 0;

MyID cannot now be the primary key of the table of course as its values will not be distinct.  The primary key will now be a composite one of MyID and TransactionDate.

Answer 4

Ok, I follow you so far, then how would I then store the data in a single table? I know how to create an append query.

Answer 5

You could write a little function like the following.  For simplicity I've included only the first and last names for comparison, but the SQL statement can easily be extended.

Public Function CompareVals(lngID As Long, ParamArray CurrentVals() As Variant) As String

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim n As Integer

    Dim var As Variant

    strSQL = "SELECT FirstName,LastName FROM tblDataYesterday " & _

        "WHERE MyID = " & lngID

    Set rst = CurrentDb.OpenRecordset(strSQL)

    For n = 0 To 1

        If CurrentVals(n) <> rst.Fields(n) Then

            CompareVals = CompareVals & ", " & rst.Fields(n).Name & ":" & CurrentVals(n)

        End If

    Next n

    CompareVals = Mid(CompareVals, 3)

End Function

You can then call it in a query like this:

SELECT MyID,

CompareVals(ContactID,Firstname,LastName) AS Changes

FROM tblDataToday

WHERE LEN(CompareVals(ContactID,Firstname,LastName)) > 0;

However, storing subsets of data in separate tables like this is very bad design.  A single table in which each subset is identified by a value in a column, e.g. the date, should be used and new data appended to it.