Hi,
My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.
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.
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.
1. Start Microsoft Access, and then open the sample database
Northwind.mdb.
2. Create a new query. In the New Query dialog box, click Design View,
and then click OK.
3. Close the Show Table dialog box. On the Query menu, point to SQL
Specific, and then click Union.
4. 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";
5. Save the query as qryMyUnion, and then close the SQL window.
6. Create a new query based on qryMyUnion, and then close the Show
Tables dialog box.
7. 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" (without the quotation marks),
and then click OK.
8. 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.
9. Save the query as qryMyUnionMakeTable, and then close the query.
10. Open table tblMyUnion.
For more information, please see the article below:
208819 ACC2000: How to Create a Make-Table Query with a Union Query
http://support.microsoft.com/?id=208819
I hope this helps! If you have additional questions on this topic, please
respond back to this posting.
Regards,
Amy Vargo
Microsoft Access Engineer
This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
