MeadInKent
| Feedback | Examples of Queries |

An Access Union Query

A union query allows you to join two similar tables or queries together to form a single set. Access does not provide a program option to create this type of query.

In this first example there are two tables, one containing current budgets and another containing older archived data. A query combining both sets of data allows enquiries and reports to be made over a longer period of time.

Table One
 BudgetDetails_Archive ox
Expense Period Budget
BedHire Jun14 350
Drugs Jun14 325
Training Jun14 50
Dressings Jul14 135
Drugs Jul14 300
BedHire Jul14 350
Table Two
 BudgetDetails  ox
Expense Period Budget
BedHire Aug14 360
Drugs Aug14 320
Drugs Aug14 130
Dressings Sep14 135
Drugs Sep14 300
BedHire Sep14 360

It is necessary to type some SQL instructions to create a union query. Create a new query and add the two table data sources. Then switch the screen from Design mode and into SQL mode.
Access Union Query


SELECT BudgetDetails.*
FROM BudgetDetails
UNION ALL
SELECT BudgetDetails_Archive.*
FROM BudgetDetails_Archive;
 Qry_Budgets_Combined  ox
Expense Period Budget
BedHire Aug14 360
BedHire Jul14 350
BedHire Jun14 350
BedHire Sep14 360
Dressings Jul14 135
Dressings Sep14 135
Drugs Aug14 130
Drugs Aug14 320
Drugs Jul14 300
Drugs Jun14 325
Drugs Sep14 300
Training Jun14 50

The two tables in this example both have the same field names. This is not necessary but they must have the same number of output fields, in the same order, and with the same or compatible data types.

There are two forms of the UNION statement. It can either be 'UNION' or 'UNION ALL'. The first consolidates the records and removes duplicates. The second is quicker and returns every record.

If two queries are created and based upon the previous budget tables, but only containing the Expense fields, a union query can be created to combine them ...

 BudgDets_Categs_Union
Expense
BedHire
Dressings
Drugs
Training
← ← ←
SELECT BudgDets_Categs.*
FROM BudgDets_Categs
UNION
SELECT BudgDets_Categs_Arc.*
FROM BudgDets_Categs_Arc;

→ → →
SELECT BudgDets_Categs.*
FROM BudgDets_Categs
UNION ALL
SELECT BudgDets_Categs_Arc.*
FROM BudgDets_Categs_Arc;
 BudgDets_Categs_Union
Expense
BedHire
Drugs
Drugs
Dressings
Drugs
BedHire
BedHire
Drugs
Training
Dressings
Drugs
BedHire

A Union query can also be used to combine two sets of data with different contents. For example you may wish to combine a table of expenditure with a table of year end forecast values. It is necessary to adjust the table data using two queries in preparation for the union query.

Original table Change Adjusted query
 CurrentExpend ox
Expense Period Expend
Dressings 6 135
Drugs 6 300
BedHire 6 360
Add a user defined forecast field with a value of zero
 CurrentExpend_Qry_V2 ox
Expense Period Expend YE_Forecast
Dressings 6 135 0
Drugs 6 300 0
BedHire 6 360 0
     
 YearEndForecast ox
Expense WhoBy Forecast
Dressings CM 435
Drugs DS 260
BedHire DS 840
BedHire DS 840
Drugs SC 120
Two user defined fields are added - a period with a value of 12 and an expenditure field with a value of zero.
 YearEndForecast_Qry_V2 ox
Expense T_Period T_Expend Forecast
Dressings 12 0 435
Drugs 12 0 260
BedHire 12 0 840
Drugs 12 0 120
Dressings 12 0 56

The union query combines the data and then an additional query (based on the union query) groups and sums the values.

 Expend_Forecast_Combined ox
Expense Period Expend YE_Forecast
Dressings 6 135 0
Drugs 6 300 0
BedHire 6 360 0
Dressings 12 0 435
Drugs 12 0 260
BedHire 12 0 840
Drugs 12 0 120
Dressings 12 0 56
← ← ←
SELECT CurrentExpend_Qry_V2.*
FROM CurrentExpend_Qry_V2
UNION ALL
SELECT YrEndForecast_Qry_V2.*
FROM yrendforecast_Qry_V2;


  Combined and consolidated data
 
 Current_Exp_and_FC ox
Expense SumOfExpend SumOfYE_Forecast
BedHire 360 840
Dressings 135 491
Drugs 300 380
 


file: ac_union_query.htm Page last modified Oct14 MeadInKent.co.uk 2014