You are here:

Using MS Access/Compare 2 tables and multiple fields, return differences



I am an intermediate user of Access. I am currently working in Access 2003 and I need to compare a 200 column (represents benefit option), 28,000 record (represents employeeID) table of benefits selection to an identical table with more recent information. I need the query to return any changes that may have occurred. The find unmatched query does not work for me because I am looking to return any changes that may have been occurred in any one of the 200 columns. Can you offer advice on how to compare these tables and return any changes to any one of the columns or records?

This requires some complex SQL involving a UNION QUERY. For example, to compare First and Last Names, assuming the IDs are all the same, you can say:

FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID
WHERE NZ(Table2.FirstName,"") <> NZ(Table1.FirstName,"")
FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID
WHERE NZ(Table2.LastName,"") <> NZ(Table1.LastName,"")

This will give you a list of all of the IDs where the FN and LN don't match up.

See my web site for more help:

Find Unmatched Query
NZ Function
UNION Queries

Be sure to visit my web site and watch my FREE 3-hour-long Microsoft Access Video Tutorial  

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Richard Rost


I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.


I am the president of and I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at

©2016 All rights reserved.