Microsoft KB Archive/94830

{|
 * width="100%"|

ACC1x: Creating Result Equivalent to SQL UNION Operator Result

 * }

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, &quot;Include ALL records from 'Table A' and only those records from 'Table B' where the joined fields are equal.&quot; 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 </li></ol>

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.

<ol> Create another new query. Add two tables (Table B and UNION Result) to the query grid.</li> Join the two tables by using the mouse to connect Key2 in Table B to Key1 in the UNION Result table.</li> Double-click the join line between the two tables to bring up the Join Properties dialog box. Select the second option, &quot;Include ALL records from 'Table B' and only those records from 'UNION Result' where the joined fields are equal.&quot;</li> 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.</li> 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.</li>  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:

<pre class="FIXEDTEXT">         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. </li>  Run the append query. The UNION Result table looks like this:

<pre class="FIXEDTEXT">     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. </li></ol>

Additional query words: action sql relational

Keywords : kbusage

Issue type : kbinfo

Technology :