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. |
 |
 |
| First, create a query called First_Outer, which has the left outer join. |
 |
 |
| Then create Second_Outer, with the right outer join. |
 |
 |
| 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 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;
