Microsoft Access Office VB VBA Help and Examples
           
             

Union Queries or Joining Queries Together

Imagine you have three tables containing similar data. You want all the data from those three tables in one query. Creating one record for each entry in each table. So if table1 had 50 records table2 had 20 records and table3 had 10 records the resulting query would have 80 records in it.

Here is the example database union.mdb and zipped union.zip

Below is another example.

Table 1

PayrollNo Name
1123John
1456Steve
1567Kevin
2356James

Table 2

PayrollNo Name
1478Contractor
1123Temp

Table 3

PayrollNoWork Done
16781/5/99
18543/4/99
11231/1/98

From the three tables you want to select all payroll numbers.

Query Results

PayrollNo
1123
1456
1567
2356
1478
1123
1678
1854
1123

Note that 1123 is listed three times. This is because it appears once in table1, once in table2 and once in table3.

This type of query is called a UNION QUERY and is not the same to design as other queries.

Design a new query. Don't bother to bring the table names in as you can't use them. Click on Query menu and choose SQL Specific and then choose Union

That should change the query screen to a blank text box. If you haven't seen this view before it's called the SQL view of queries and it's where really sad propeller heads write their queries rather that using the intuitive interface MS gave you. Unfortunately Microsoft decided not to give you an interface to create SQL specific queries, so you have to type it in yourself.

For the above example the SQL to be typed would be

SELECT [PayrollNo]
FROM [Table1]

UNION ALL SELECT [PayrollNo]
FROM [Table2];

UNION ALL SELECT [PayrollNo]
FROM [Table3];

What that means in English is. Select all PayrollNo from table1 add to it all PayrollNo in table 2 and add to that all PayrollNo in table3.

If we left out the ALL word in the second two statements 1123 would only have been returned once. See below

SELECT [PayrollNo]
FROM [Table1]

UNION SELECT [PayrollNo]
FROM [Table2];

Query Results (Without ALL statement)

PayrollNo
1123
1456
1567
2356
1478
1678
1854

You can also return more that one field by seperating field names with a comma on the select statement. All fields must be of the same type and specified in the same order.

SELECT [PayrollNo],[Name]
FROM [Table1]

UNION SELECT [PayrollNo],[Name]
FROM [Table2];

Query Results (With names)

PayrollNoName
1123John
1456Steve
1567Kevin
2356James
1478Contractor
1123Temp

Note that 1123 is listed twice now. That's because the names weren't unique. One is John the other is temp.

This time we are going to specify a criteria. We want all records whose payrollno is greater than 1400.

SELECT [PayrollNo],[Name]
FROM [Table1]
WHERE [PayrollNo] > 1400

UNION SELECT [PayrollNo],[Name]
FROM [Table2]
WHERE [PayrollNo] > 1400;

Note we have to specify the criteria twice. Once for each table. You can specify different criteria for each table if you want.

Query Results (Specifc criteria)

PayrollNoName
1456Steve
1567Kevin
2356James
1478Contractor

Finally you can join different fields together if necessary using the as clause

SELECT [PayrollNo],[Name]
FROM [Table1]

UNION SELECT [PayrollNo],cstr([Work Done]) AS 'Name'
FROM [Table2]

Note we have to convert the date into a string. Obviously the example given is not a useful one but knowing this trick you will soon find a use for it.

I use this trick all the time to convert flat data imported into a database into relational data without having to write a single line of code.

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.