Article ID: 209062
Article Last Modified on 7/13/2004
APPLIES TO
- Microsoft Access 2000 Standard Edition
This article was previously published under Q209062
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
SUMMARY
This article demonstrates a shortcut method for creating an SQL union query using a second scratchpad query to create the second half of the SQL UNION statement.
MORE INFORMATION
To create a union query that returns all the customers and suppliers in London in the sample database Northwind.mdb, follow these steps:
- Start Microsoft Access and open the sample database Northwind.mdb.
- In the Database window, click Queries under Objects, and then click New to create a new query.
- In the New Query dialog box, click Design View, and then click OK.
- In the Show Table dialog box, click Customers, click Add, and then click Close.
- Add the CompanyName, ContactName, and City fields to the query grid.
- In the Criteria row for the City column, type London.
- On the View menu, click SQL View.
The SQL statement displayed is the first half of the union.
- Leave the first query open and repeat steps 2 through 6 to create a second new query based on the Suppliers table.
This is the scratchpad query.
- On the View menu, click SQL View. Select the entire SQL statement except the ending semicolon (;).
- Press CTRL+C to copy the selected text to the clipboard.
- Close the scratchpad query without saving it, and then switch back to the original query.
- Position the insertion point before the semicolon (;) at the end of the SQL statement. Press ENTER, and then type the keyword UNION with a space after it. Then paste the text from the clipboard by pressing CTRL+V.
This action creates the second half of the union. The semicolon (;) should be the last character in the SQL statement.
- 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 unavailable.
Tips
To tell which table the data comes from, switch back to the query's SQL view, and then 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];
REFERENCES
For more information about union queries, click Microsoft Access Help on the Help menu, type combine data in fields from two or more tables using a union query in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Additional query words: queries sql-specific short cut outer join
Keywords: kbhowto kbinfo KB209062
