Compare difference between two tables in MS Access Query

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

inner_join

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)

left_outer_join

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)

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.

compare_data_03

Create a query in Query Design.

compare_data

Add the two tables that we need to compare

compare_data_02

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.

compare_data_05

The result will show records where 3 fields are exactly identical.

compare_data_07

Records exist in 2013 but not 2014

Based on the last Query, double click on the first line between two tables.

compare_data_05

Select the second option to create Left Join.

compare_data_12

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.

compare_data_08

Click Run to see the result.

compare_data_11

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)

compare_data_13

Add criteria Is Null for 2014 Empl ID

compare_data_06

Press Run, the result shows data containing Null 2014 Empl ID, that is the difference you are looking for.

compare_data_14

Records exist in 2014 but not 2013

Repeat the steps in Left Join, except that we select the third option in Join Property.

compare_data_09

This time the arrows point to the left, and then add criteria Is Null for 2013 Empl ID

compare_data_04

Press Run to see result.

compare_data_10

Outbound References

http://www.techonthenet.com/sql/joins.php

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *