ACC1x: Creating Result Equivalent to SQL UNION Operator Result

ID: Q94830

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.

Additional query words: action sql relational

Keywords : kbusage QryUnion
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo