Microsoft KB Archive/208819

= ACC2000: How to Create a Make-Table Query with a Union Query =

Article ID: 208819

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q208819



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

This article applies only to a Microsoft Access database (.mdb).



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.</li> 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.</li> 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.</li> Save the query as qryMyUnionMakeTable, and then close the query.</li> Open table tblMyUnion.</li></ol>

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

<div class="references_section">