DataWright Information Services

Consulting and Resources for Excel and Access

Two ways to compare lists in Access

This article is based on a question from an on-line forum. If you have two lists of values in different tables, how do you compare them side by side? There are several ways to do this, and this page will show two of them:

  • If you only have two lists to compare you can create two mismatch (outer join) queries and combine them with a Union query. Go to article
  • If you need to compare 3 or more lists, it is simpler to create a new table, append the list items to that table, and view the results in a crosstab query. Go to article
  • Download a sample file here

Using two outer joins and a union query

How it works

Normally, when you create a query with two tables you will only see records with matching data on both sides of the query join. This is referred to as an inner join. However, Access (and other relational databases) let you create outer joins too; you see all records on one side of the join, and matching records (and gaps) on the other side of the join. So, assuming that you have tblList1 and tblList2 you would do the following:

  • Create a left outer join between tblList1 and tblList2; the result is all records from tblList1, with matches and gaps from tblList2.
  • Create a right outer join between tblList1 and tblList2; this time you get all records from tblList2, with matches and gaps from tblList1.
  • If you compare these queries you will see duplicates; all records where a particular account has values in both lists. Removing them is simple in Access. By creating a Union query to combine the lists, you will get a final list with no duplicates.

Setting it up

The two tables in this example each have the same field layout, and 6 records. tblList1 in DataSheet view tblList2 in DataSheet view
First, create a query called First_Outer, which has the left outer join. the design of first_outer first_outer in DataSheet view
Then create Second_Outer, with the right outer join. Second_Outer in Design view Second_Outer in DataSheet view
Finally, create the union query to display the final list. If you haven't used a union query before, they cannot be created in Design view; you must create a Union query in SQL. See here for more information.   the union query displays the final list

The SQL for the queries

The SQL for these 3 queries is shown below. You can also see it by going into SQL View in the sample database.

First_Outer

SELECT tblList1.Account, tblList1.Balance AS Bal_1, tblList2.Balance AS Bal_2
FROM tblList1 LEFT JOIN tblList2 
ON tblList1.Account = tblList2.Account;

Second_Outer

SELECT tblList2.Account, tblList1.Balance AS Bal_1, tblList2.Balance AS Bal_2
FROM tblList1 RIGHT JOIN tblList2 
ON tblList1.Account = tblList2.Account;

qunionBalances

SELECT * FROM First_Outer
UNION 
SELECT * FROM Second_Outer;

Limitations

This method is relatively simple to use for two lists. If you need to compare 3 or more lists it becomes increasingly difficult to capture all of the possible combinations, and a more modular approach is called for. It's much easier to expand the second approach, of appending the list data to another table and summarising the result in a crosstab query.

Appending to an accessory table and using a crosstab query

The second technique uses three queries. The first is a Union query that combines the records from the tables, along with an extra field indicating the source table. As more tables need to be compared, you simply add each table to this query. The SQL is shown below:

qUnionLists

SELECT tblList1.Account, tblList1.Balance, "List1" AS Source
FROM tblList1
UNION
SELECT tblList2.Account, tblList2.Balance, "List2" AS Source
FROM tblList2;

The second query appends this data to a table called tblMainList. The SQL is shown below:

qappMainList

INSERT INTO tblMainList ( Account, Balance, Source )
SELECT qUnionLists.Account, qUnionLists.Balance, qUnionLists.Source
FROM qUnionLists;

The third query is a crosstab that displays the final result. As you add more tables to the first Union query, additional columns are created in the crosstab. So, although the data needs to be written to an accessory table, this method is far easier to maintain than the first. The SQL is shown below:

ctbShowLists

TRANSFORM Sum(tblMainList.Balance) AS SumOfBalance
SELECT tblMainList.Account
FROM tblMainList
GROUP BY tblMainList.Account
PIVOT tblMainList.Source;