The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
SUMMARY
The SQL UNION operator is not supported in Microsoft Access. However, you
can obtain an equivalent result (a UNION product) by using the following
two-step process to create two queries: a make-table query and an append
query.
MORE INFORMATION
The product of a UNION includes all records from two related tables--those
that match on the related key field and those that do not match on the
related key field.
For example, say you have two tables, Table A and Table B. Table A contains
the following fields and records:
Key1 Field A
----------------
1 Seattle
2 Portland
4 Chicago
Table B contains the following fields and records:
Key2 Field B
------------------
1 Washington
3 Oregon
5 Illinois
The UNION product of the two tables would look like this:
Key Field A Field B
-------------------------------
1 Seattle Washington
2 Portland
3 Oregon
4 Chicago
5 Illinois
Use the following two-step process to obtain a UNION product in Microsoft
Access:
Step One: Collect the Records that Have a Matching Key Value
- Create a query, and add the two tables (Table A and Table B) to the
query grid.
- Join the two tables by using the mouse to connect Key1 in Table A to
Key2 in Table B.
- Double-click the join line between the two tables to bring up the Join
Properties dialog box. Select the second option, "Include ALL records
from 'Table A' and only those records from 'Table B' where the joined
fields are equal."
- Add three fields to the query grid: Key1 from Table A, Field A from
Table A, and Field B from Table B. You can use the mouse to drag these
fields onto the query grid.
- Choose Make Table... from the Query menu. The Query Properties
dialog box appears. The default selection is Current Database under
Make New Table. Enter an appropriate table name (for example,
UNION Result) for the result table, and choose the OK button.
- Run the query to create the new table (UNION Result). It will contain
the following fields and records:
Key1 Field A Field B
----------------------------------
1 Seattle Washington
2 Portland
4 Chicago
Step Two: Add the Rest of the Records to the Result
To complete the UNION, you need to add the records in Table B that do not
have a matching key field value in Table A.
- Create another new query. Add two tables (Table B and UNION Result) to
the query grid.
- Join the two tables by using the mouse to connect Key2 in Table B to
Key1 in the UNION Result table.
- Double-click the join line between the two tables to bring up the Join
Properties dialog box. Select the second option, "Include ALL records
from 'Table B' and only those records from 'UNION Result' where the
joined fields are equal."
- Add three fields to the query grid: Key2 from Table B, Field B from
Table B, and Key1 from UNION Result. You can use the mouse to drag
the fields onto the query grid. You do not need Field A from UNION
Result because UNION Result already contains all the rows from Table A.
- Choose Append... from the Query menu. The Query Properties dialog box
appears. Ensure that UNION Result is in the Table Name field under
Append To. Then choose the OK button.
- Verify that the query grid how has a line labeled Append To. Choose
Table Names from the View menu to show table names on the query grid.
Now the query grid should look something like this:
Field: Key2 Field B Key1
Table: Table B Table B UNION Result
Sort:
Append To: Key1 Field B
Criteria: Is Null
Note that the Key1 field from the UNION Result table is not appended
to anything and it has an Is Null restriction on it. This ensures that
no records from UNION Result are duplicated and appended to it when you
run the append query.
- Run the append query. The UNION Result table looks like this:
Key1 Field A Field B
----------------------------------
1 Seattle Washington
2 Portland
3 Oregon
4 Chicago
5 Illinois
As you can see, record 1 has a match on the joined fields, but all the
other records are represented only once. This is the correct UNION
product. If the Key1 values in UNION Result are not in ascending order,
it is because there is no primary key on that column.