| Feedback | Examples of Queries | |
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
|
Table Two
|
SELECT BudgetDetails.* FROM BudgetDetails UNION ALL SELECT BudgetDetails_Archive.* FROM BudgetDetails_Archive; |
|
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 ...
|
← ← ← 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; |
|
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 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Add a user defined forecast field with a value of zero |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Two user defined fields are added - a period with a value of 12 and an expenditure field with a value of zero. |
|
The union query combines the data and then an additional query (based on the union query) groups and sums the values.
|
← ← ← SELECT CurrentExpend_Qry_V2.* FROM CurrentExpend_Qry_V2 UNION ALL SELECT YrEndForecast_Qry_V2.* FROM yrendforecast_Qry_V2; | |||||||||||||||||||||||||||||||||||||||||||||||||||
Combined and consolidated data | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
file: ac_union_query.htm | Page last modified Oct14 | © MeadInKent.co.uk 2014 |