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

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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.

MORE INFORMATION

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" (without the quotation marks), 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.

REFERENCES

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