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;