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


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q132070

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.

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 ACC97: Microsoft Access 97 Sample Queries Available in Download Center

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

For more information about make-table queries, search the Help Index for "make-tables," or ask the Microsoft Access 97 Office Assistant.
Modification Type:MajorLast Reviewed:5/7/2003
Keywords:kbhowto KB132070
©2002 Microsoft Corporation. All rights reserved.