This Access tutorial explains how to compare difference between two tables in MS Access Query
Compare difference between two tables in MS Access Query
Access does not have a built-in Function to compare difference, but since we can write SQL and design Query in Access, we can make use of the properties of SQL JOIN to compare difference.
There are three kinds of JOIN in SQL – Inner Join, Left Join, Right Join.
Assume that you have two tables, table1 and table2, where you join them using a key Field “Employee ID”.
Inner Join
Return rows that have the common Employee ID in both tables
Left Join (Left Outer Join)
Return all rows in table1, plus fields in table2 where the Employee ID are matched (same as Excel vlookup, using table1 as a base, and then vlookup the employee ID in table2 for the required field)
In Excel, if table1 fails to vlookup key from table2, #N/A is returned. In Access, those #N/A data are known as Null value.
The whole point of Left Join is to find out those fields containing Null value, then we know what keys are present in table1 but not present in table2.
Right Join (Right Outer Join)
Same as Left Join, except that table1 becomes table2, table2 becomes table1. (using table2 as a base, and then vlookup the employee ID in table1 for the required field)
Excel Vlookup vs Access Query, which one is better for data comparison?
If your job requires to compare difference frequently, think about Access.
| Factors considered in data comparison | Excel | Access |
| Performance dealing with mass data | Win | |
| Performance dealing with mass formula | Win | |
| Handling multiple keys for matching | Win | |
| Time required to build comparison Query/Formula | Win | |
| Accuracy of handling Null data | Win | |
| Look for mistakes made in Query/Formula | Win | |
| File size | Win | |
| Re-usability of comparison template | Win |
Example – compare difference between two tables
Assume that we have 2 staff lists, one is 2013 and the other is 2014. During the year, some employees have transferred to another department. Now we want to compare difference between these two tables.
Create a query in Query Design.
Add the two tables that we need to compare
To fully compare the queries, we need to make 3 comparisons. (In Excel, we vlookup in both sides to find out the variance)
1) Which records are completely the same
2) Which records exist in 2013 but not 2014
3) Which records exist in 2014 but not 2013
Records that are completely the same
In the Query Design, create an Inner Join (a straight line across two fields) for the key fields (say, Empl ID and Name) and fields you need to compare (Department). To create a join, simply drag from 2013 field over 2014 field.
The result will show records where 3 fields are exactly identical.
Records exist in 2013 but not 2014
Based on the last Query, double click on the first line between two tables.
Select the second option to create Left Join.
Repeat the above steps for the other two lines. Now the lines turn into arrows pointing from left to right, indicating it is a Left Join.
Click Run to see the result.
All 2013 data are present in the result, and we try to lookup the data from 2013 to see whether we can find the Empl ID+Name+Department key in 2014.
In Vlookup concept, those blank values are #N/A value (known as Null value in SQL)
Add criteria Is Null for 2014 Empl ID
Press Run, the result shows data containing Null 2014 Empl ID, that is the difference you are looking for.
Records exist in 2014 but not 2013
Repeat the steps in Left Join, except that we select the third option in Join Property.
This time the arrows point to the left, and then add criteria Is Null for 2013 Empl ID
Press Run to see result.
Outbound References
http://www.techonthenet.com/sql/joins.php
