Union query
A union query combines fields
(columns) from one or more tables or queries into one field or column in the query's
results. For example, if you have six vendors that send new inventory lists each
month, you can combine these lists into one result set using a union query, and
then create a make-table query based on the union query to make a new table.
Basic
union query
The following
union query consists of two SQL SELECT statements that return the company names
and cities that are in Brazil, from both the Suppliers and Customers tables.
A union query takes its column names from the column names in the first table
or SELECT statement. If you want to rename a field in the results, use the AS
clause to create an alias for the fields.
Renaming
fields in a union query
The following union query
renames the Company Name field to "Supplier/Customer Name" in the query output:
SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Suppliers]
UNION SELECT [CompanyName] AS [Supplier/Customer Name], [City]
FROM [Customers];
Make-table
query
Creates a new table
from all or part of the data in one or more tables.
In one application that required
the combining of two tables with similar data, I took the following approach:
- First I created the Union query called "reordered_2."
- Next I created a Make-Table query to create a new table that
included all of the attributes from reordered_2 except the ID.
- Next I added an Autonumber column to the new table to provide
an primary key.