ACC: How to Create a Make-Table Query with a Union Query

Moderate: Requires basic macro, coding, and interoperability skills.

Microsoft Access SQL does not allow you to use the INTO clause (a clause needed to create a make-table query) within a union query. Therefore, you cannot directly create a make-table query; you must first create a union query, and then use the results of that query in the make-table query. This article demonstrates how to do so.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

182568  (http://support.microsoft.com/kb/182568/EN-US/ ) ACC97: Microsoft Access 97 Sample Queries Available in Download Center

In order to create a table from a union query, you must first define the union query, and then create a make-table query based upon the union query results. To do so, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb (or the NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb or NWIND.MDB file or perform these steps on a copy of these databases.

1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
2. Create a new query. On the Query menu, click SQL Specific, and then click Union on the menu that appears.
3. Type the following lines into the SQL window:
   

         SELECT CompanyName, City, "Customers" as [Relationship]
         FROM Customers
         WHERE Country = "Brazil"
         UNION SELECT CompanyName, City, "Suppliers"
         FROM Suppliers
         WHERE Country = "Brazil";
   						

   
   NOTE: In version 2.0, the Company Name field has a space.
4. Save the query as MyUnion, and then close the SQL window.
5. Create a new query based on the MyUnion query, and then close the Show Tables dialog box.
6. Double-click the MyUnion query's asterisk to add all the fields to the query's output. On the Query menu, click Make Table. In the Table Name box, type MyUnion Table, and then click OK.
7. On the Query menu, click Run, and then click the OK button on the dialog box that informs you how many records will be copied into the new table.
8. Save the query as "MyUnion Make Table," and then close the query.
9. Open MyUnion Table. Note that the MyUnion Make Table query created 10 records from the Customers and Suppliers tables whose Country field contained Brazil.

For more information about union queries, search the Help Index for "union queries," or ask the Microsoft Access 97 Office Assistant.

For more information about make-table queries, search the Help Index for "make-tables," or ask the Microsoft Access 97 Office Assistant.