HOME MS ACCESS MS OUTLOOK MS EXCEL VB and VBA FREE PROGRAMS PERSONAL ACCESS GENERAL QUERIES FORMS REPORTS MODULES RANTS TUTORIALS 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 zipped union.zip Below is another example. Table 1 PayrollNo Name 1123 John 1456 Steve 1567 Kevin 2356 James Table 2 PayrollNo Name 1478 Contractor 1123 Temp Table 3 PayrollNo Work Done 1678 1/5/99 1854 3/4/99 1123 1/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) PayrollNo Name 1123 John 1456 Steve 1567 Kevin 2356 James 1478 Contractor 1123 Temp 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) PayrollNo Name 1456 Steve 1567 Kevin 2356 James 1478 Contractor 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.