DataWright Information Services

Consulting and Resources for Excel and Access




Creating Union queries

  • Download

What is a Union Query?

A Union query is used to combine records from different tables or
queries that have the same structure. The result is a non-updatable query
that combines all the records from the different data sources. The fields
don’t have to have identical names in the queries that make up the union query,
but each query needs to have the same number of fields in the same order,
and the data types must match. A classic example is where you have
created several crosstab queries, each summarising a different category or
item for a complex report. In order to build the report you need to combine
those queries into one data source for the report to work with.

How do I create a Union Query?

Build the feeder queries first

In the example, there are three crosstabs: ctbNetRevenue, ctbCostOfSales
and ctbGrossProfit. All have the same structure, and are based on the same
base query. ctbNetRevenue is shown in Design view:
the SQL is in
Listing 1 below.

Listing 1: The first feeder query

TRANSFORM Sum(qrySales.NetRevenue) AS SumOfNetRevenue
SELECT qrySales.FinYear, qrySales.Branch, "Net Revenue" AS Category
FROM qrySales
GROUP BY qrySales.FinYear, qrySales.Branch, "Net Revenue"
PIVOT Format([CalendarMonth],"mmm")	

Create each query, check that they give the correct data, then use them as
the basis for the next step.

Create a new, blank query

Union queries are SQL-specific queries: you can only create them in the
SQL view. When you create the new query, click Close when prompted for
tables or queries, then click the SQL icon at the top left of the screen to
see the SQL view.

Assemble the SQL statements

Paste the SQL from each query one below the other. You need to separate
each SQL statement with the UNION keyword. (If you use UNION, you will get
distinct records — if one record occurs in more than query, you will only
see it once. To get all records, including repeats, use UNION ALL instead,
as shown below).
You have two options here. If you intend to keep the intermediate queries
you can just string them together, as in Listing 2.

Listing 2: Use the query names, connecting them with UNION or
UNION ALL

SELECT * FROM ctbRevenue
UNION ALL
SELECT * FROM ctbCostOfSales
UNION ALL 
SELECT * FROM ctbProfit;

If you want to remove the intermediate queries from the database, you
will need to use the full SQL from each query, as in Listing 3.

Listing 3: Use the full SQL from each of the queries

TRANSFORM Sum(qrySales.NetRevenue) AS SumOfNetRevenue
SELECT qrySales.FinYear, qrySales.Branch, "Net Revenue" AS Category
FROM qrySales
GROUP BY qrySales.FinYear, qrySales.Branch, "Net Revenue"
PIVOT Format([CalendarMonth],"mmm")
UNION ALL
TRANSFORM Sum(qrySales.CostOfSales) AS SumOfCostOfSales
SELECT qrySales.FinYear, qrySales.Branch, "Cost Of Sales" AS Category
FROM qrySales
GROUP BY qrySales.FinYear, qrySales.Branch, "Cost Of Sales"
PIVOT Format([CalendarMonth],"mmm")
UNION ALL
TRANSFORM Sum(qrySales.GrossProfit) AS SumOfGrossProfit
SELECT qrySales.FinYear, qrySales.Branch, "Gross Profit" AS Category
FROM qrySales
GROUP BY qrySales.FinYear, qrySales.Branch, "Gross Profit"
PIVOT Format([CalendarMonth],"mmm");

The finished union query combines all of the data, categorised by
financial year, market and category, split out by month. Note that
all three queries have the same field names, in the same sequence.
If you fail to do this, you will most likely get an error when you
try to run the query.

At this point it is ready to be used in a summary report, or
written out to a new table using a Make Table query.