Work anywhere from any device with Microsoft 365

Upgrade to Microsoft 365 to work anywhere with the latest features and updates.

Upgrade now

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. 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!

 

Question Info


Last updated December 13, 2019 Views 3,386 Applies to:

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.

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

Se la risposta ti ha aiutato "Vota Utile".
Se ha risolto il problema "Segna Risposta.
Rome, Italy

1 person was helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.  

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.

_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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.
_____________________
Ken Sheridan,
Stafford, England

"Don't write it down until you understand it!" - Richard Feynman

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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


Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

* Please try a lower page number.

* Please enter only numbers.

* Please try a lower page number.

* Please enter only numbers.