ACC2000: How to Create a Make-Table Query with a Union Query (208819)

SUMMARY

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 this.

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center

MORE INFORMATION

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

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

Start Microsoft Access, and then open the sample database Northwind.mdb.
Create a new query. In the New Query dialog box, click Design View, and then click OK.
Close the Show Table dialog box. On the Query menu, point to SQL Specific, and then click Union.

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";

Save the query as qryMyUnion, and then close the SQL window.
Create a new query based on qryMyUnion, and then close the Show Tables dialog box.
Double-click the qryMyUnion 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 tblMyUnion, and then click OK.
On the Query menu, click Run, and then click Yes on the dialog box that informs you how many records will be copied into the new table.
Save the query as qryMyUnionMakeTable, and then close the query.
Open table tblMyUnion.

Note that the query qryMyUnionMakeTable created 10 records from the Customers and Suppliers tables whose Country field contained "Brazil."

REFERENCES

For more information about union queries, click Microsoft Access Help on the Help menu, type what is an sql query and when would you use one in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about make-table queries, click Microsoft Access Help on the Help menu, type create a new table from the results of a query with a make-table query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.