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

For future reference, rather than piggy-backing a question on an old thread, start a new thread and include a hyperlink to any earlier thread to which you wish to refer.

The CompareVals function which I wrote was aimed specifically at the OP's requirements, i.e. to detect differences between two rows known to exist.  Because the function is predicated on this I don't think it would be capable of amendment to do what you are asking.

To detect what you have termed 'removed records' or 'new records' the usual solution would be to use an OUTER JOIN in a query and test for a NULL key in the table on the right side or left side of the join as appropriate.  To test for both simultaneously a UNION of LEFT and RIGHT OUTER JOINs can be used.