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

Ken Sheridan,

I apologize for jumping in on an older question.

I ran across this post and found it to be very helpful. Thank you.

After testing the code I noticed there was no clear designation in Changes of

New Records (records in tblDataToday and not in tblDataYesterday)

And no clear designation in Changes of

Removed Records (records in tblDataYesterday and not in tblDataToday)

I have attempted to modify the function to accomplish this but have not been successful.

I thought I would reach out to see if this is something you would be willing to help with.

Thank you for your attention to this matter.

Accel

Answer 2

That is fine, I completely understand. Thank you so much for your help!  Can anyone else help me with this problem?

Answer 3

Is there any way to display the changes into separate columns such as PartNumber and Cost?

You could possibly loop through a recordset and build a string expression of those columns which have changed, then build this into an SQL statement which returns only the rows/columns where values have changed.  Then create and open a temporary querydef object.  Unfortunately demands of grandchildren in relation to the current festivities don't allow me time to tackle it at present.

Answer 4

Ken, you are awesome!  Thank you so much!

This is working great except for one more thing, (it's always one more thing, isn't it?).  Is there any way to display the changes into separate columns such as PartNumber and Cost?

I changed the field names to be more realistic to the type of data I will be working with.

Answer 5

Mea culpa!  ContactID was a hangover from my testing of the function with my own contacts data.  It should have been changed to MyID;  though I would recommend something more semantically explicit than MyID as a column name.

The error you experienced would occur if a matching row does not exist in the tblDataYesterday table.  You can avoid that, and also improve the code so that the number of values in the parameter array is variable, as follows:

    If Not (rst.BOF And rst.EOF) Then

         For n = 0 To UBound(CurrentVals)

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

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

             End If

         Next n

         CompareVals = Mid(CompareVals, 3)

    End If

You can test the function works by calling it in the debug window, e.g. in my case by comparing a row for Martha Sheridan in my contacts table, which has a ContactID of 5, with myself, passing in literal values as the parameter array.  I get this result:

? CompareVals(5,"Ken","Sheridan")

FirstName:Ken

If I compare contact 347 (Steve Smith) with myself:

? CompareVals(347,"Ken","Sheridan")

FirstName:Ken, LastName:Sheridan

In the first example it tells me the first name has changed, in the second that both first and last names have changed.