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.