Union Queries are useful for combining data from more than one Table or from other Queries while maintaining the original data physically separate.
Assume that the data Tables from different branches (branch1, branch2 & branch3) are linked directly into our main system in separate files and our job is to combine the contents and prepare monthly Reports, Charts etc. We can use Append Queries and combine the data into a separate Table and prepare what we want. Then that is duplication of information. Instead we can combine the data with the help of a Union Query and use that as source for our further processing tasks. The source data files will remain physically separate, branch locations can continue updating information on them.
We cannot place Tables or Queries on the Query Design surface, as we do with other type of Queries instead we have to write the SQL String manually on the SQL Window directly. The SQL Syntax is not that hard to memorize and with little practice you can write them. First have a look at the sample Tables given below, their contents, number of fields in both tables, Field Names, Values, and Field Types etc.
Let us look into an example. The contents of the above tables we will combine together with the help of a Union Query.
- In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
- In the New Query dialog box, click Design View, and then click OK.
- Without adding tables or queries, click Close in the Show Table dialog box.
- On the Query menu, point to SQL Specific, and then click Union.
- Enter SQL SELECT statements to combine data from tables or Queries.
TABLE [Branch1] UNION SELECT * FROM [Branch2]; OR SELECT * FROM [Branchl] UNION SELECT * FROM [Branch2];
Copy and paste one of the above SQL string into the SQL design window and save the Query with the name UNION1 or with any name you prefer. Open the Union Query in normal datasheet view.
The output of the above query is given below:
The above statement will combine data from both Branchl and Branch2 Tables and sort the data on the first field. Any duplicate records found in them will be suppressed from the output. If you have more than 2 Tables or Queries you can repeat the part UNION SELECT * FROM [xxxxxxxx] (where [xxxxxxxx] stands for table or query name) for each table or query and place a semi-colon (;) at the end.
We can interpret the action that goes into above SQL statement and read it as "Take all the field values of all the records from the table Branchl, select all the field values from all the records from Branch2 and combine them together, remove duplicates out of the resultant recordset and sort the data in ascending order based on values in the first column."
You cannot use field types MEMO, OLE or Hyperlink in the Source tables or Queries in the above example. But if you add the magic word ALL after the word UNION then all the fields (including Memo, OLE & Hyperlink) are taken in, no sorting is carried out, duplicates are not removed and all the records from all Source Tables or Queries will be given back to you in the order the Tables or Queries are appearing in your SQL Statement.
Let us try another trick. We will take the first Table contents twice, instead of joining another table and let us see the difference. Modify the SQL String as follows or create a new Query with the following statement:
TABLE Branch1 UNION SELECT *FROM Branch1;
Open the Query in DataSheet View. What happened? The second set of data vanished. Union Query automatically suppresses duplicates from its outputs unless we explicitly state otherwise. Modify the SQL String by adding our magic word ALL immediately after the word UNION and then open the Query in Datasheet view. Now the Table contents will appear twice in the output.
There are certain rules that will not be overlooked by the Union Query, like all Source Tables or Queries should have equal number of fields, sorting Field Name(s) must be from the first Table or Query etc. and there is no magic word to over-rule this.
If you mixed the order of the fields and different data types (Number, Text) in different positions the output will be given to you in the order of the fields you have provided and the Column headings will be taken from the First Table or Query only. If you made a mistake in placing the field order differently, in any of the source table or query, you will come to know about it only when you take the time to look through the output.
If you look at the sample output given above, the Age and Country field values from the Table Branch2 are placed under the Salary and Address fields.
We will make little change on the structure of the second Table Branch2 and see how the output behaves after that, to understand what can go wrong, if we are not careful when handling this type of Queries.
- Open the Table Branch2 in design view.
- Click and drag the field Age and place it after the field FirstName.
- Click and drag the Country field and place it next to the field Age.
- Save the Table Structure.
- Open the Union Query and inspect the output.
The shifted field values will appear exactly on the new location moving other values forward up to the point from where we have removed the Age and Country fields to the left, ignoring the data types of other member fields in the Union Query and you will not get any warnings.
In the above example we have taken all the fields from the member tables by giving the * symbol after the SELECT clause. If you need only selected fields from the member tables or queries you must specify them in the correct order.
To select EmployeelD, FirstName and Birth Date from both tables and to get the output sorted on FirstName field we can write:
SELECT [EmployeelD], [FirstName], [BirthDate]FROM [Branch1] UNION ALL SELECT [ID], [FirstName], [BirthDate] FROM [Branch2] ORDER BY [FirstName];
The ORDER BY Clause will take Field Names only from the Source Object appearing in the first SELECT statement otherwise it will show error when you attempt to view the output.
If you open the Table Branch2 in Datasheet view you can select and drag Age and Address Columns to any position for your viewing purposes. If you have opted to save the layout after these changes you will always see the field values in the changed position in datasheet view, irrespective of the physical position of these fields in the Table structure or Query Design. Remember, never depend on the datasheet view of table or query to determine the field layouts for the Union Query. Always look for the field order in the table structure or Select Query Design View before adding them into the Union Query in the same order.
When you are designing Union Queries having hundreds or thousands of records in the output, always make a point to check through the object level recordset at least once to ensure that they are in order. Once we are sure that the output is appearing correctly then you can apply sorting, suppress duplicates or include conditions to filter data.
When applying conditions you must specify Criteria at each Table or Query level.
For example: if you want to select employees born on 31st December 1960 or earlier from both tables then you will write the SQL in the following manner:
SELECT * from [Branch1] WHERE ([BirthDate] <=#12/31/1960#) UNION ALL SELECT * from [Branch2] WHERE ([BirthDate] <=#12/31/1960#);
This will take records from each member table or query that satisfies the criteria. Remove the WHERE clause from any one position and check the output again.