ACC: Shortcut Method for Creating an SQL Union Query (114731)

MORE INFORMATION

The following example demonstrates how to create a union query that returns all the customers and suppliers in London in the sample database Northwind.mdb (or NWIND.MDB in version 2.0).

1. Start Microsoft Access and open Northwind.mdb.
2. Create a new query based on the Customers table.
3. Add the CompanyName, ContactName, and City fields to the query grid.
   
   NOTE: In version 2.0, there is a space in Company Name and Contact Name field names.
4. In the Criteria row for the City column, type London.
5. On the View menu, click SQL View (or SQL in Microsoft Access 7.0 and earlier). The SQL statement displayed is the first half of the union.
6. Leave the first query open and create a second new query based on the Suppliers table. This will be the "scratchpad" query.
7. Repeat steps 3 and 4 using the Suppliers table.
8. On the View menu, click SQL View (or SQL in Microsoft Access 7.0 and earlier). Select the entire SQL statement, except the ending semicolon (;).
9. Press CTRL+C to copy the selected text to the Clipboard.
10. Close the scratchpad query without saving it, and then switch back to the original query.
11. Position the insertion point before the semicolon (;) at the end of the SQL statement. Type the keyword union with a space before and after it.
12. Position the insertion point before the semicolon (;) at the end of the statement. Paste the text from the Clipboard by pressing CTRL+V. This creates the second half of the union.
13. Run the query. Note that the title bar indicates that it is a union query, and that the Design view button on the toolbar is disabled.

Tips

NOTE: In the examples of SQL statements in the tips below, there are differences in the SQL syntax for Microsoft Access 2.0 and 7.0. In Microsoft Access 2.0 and 7.0, the word DISTINCTROW appears after each SELECT, for example:

   SELECT DISTINCTROW Customers.CompanyName
				

In Microsoft Access 2.0, because the field names contain spaces they are enclosed in square brackets ([]), for example:

   SELECT DISTINCTROW Customers.[Company Name]
				

In Microsoft Access 2.0, the field names used in a WHERE clause are not surrounded by an extra set of parentheses (), and they appear like this:

   WHERE ((Suppliers.City="London"))
				

To be able to tell which table the data comes from, switch back to the query's SQL view and add the table name (preceded by a comma and enclosed in quotation marks) to the end of each SELECT statement, as in this example:

   SELECT Customers.CompanyName, Customers.ContactName,
   Customers.City, "Customers"
   FROM Customers
   WHERE (((Customers.City)="London"))
   UNION SELECT Suppliers.CompanyName, Suppliers.ContactName,
   Suppliers.City, "Suppliers"
   FROM Suppliers
   WHERE (((Suppliers.City)="London"));
				

If you include the table name in the query, it appears in a fourth column called "Expr1003," when you run the query. To give the new column a more meaningful name, add an alias to it. For example, add the alias "[Source Table]" to the first SELECT statement as shown in this example:

   SELECT Customers.CompanyName, Customers.ContactName,
   Customers.City, "Customers" AS [Source Table]
   FROM Customers
   WHERE (((Customers.City)="London"))
   UNION SELECT Suppliers.CompanyName, Suppliers.ContactName,
   Suppliers.City, "Suppliers"
   FROM Suppliers
   WHERE (((Suppliers.City)="London"));
				

If you want to sort your query, add an ORDER BY statement to the last SELECT clause in the UNION statement as shown below:

   SELECT Customers.CompanyName, Customers.ContactName,
   Customers.City, "Customers" AS [Source Table]
   FROM Customers
   WHERE (((Customers.City)="London"))
   UNION SELECT Suppliers.CompanyName, Suppliers.ContactName,
   Suppliers.City, "Suppliers"
   FROM Suppliers
   WHERE (((Suppliers.City)="London")) ORDER BY [ContactName];