Microsoft KB Archive/209062

= ACC2000: Shortcut Method for Creating an SQL Union Query =

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:
 * 1) Start Microsoft Access and open the sample database Northwind.mdb.
 * 2) In the Database window, click Queries under Objects, and then click New to create a new query.
 * 3) In the New Query dialog box, click Design View, and then click OK.
 * 4) In the Show Table dialog box, click Customers, click Add, and then click Close.
 * 5) Add the CompanyName, ContactName, and City fields to the query grid.
 * 6) In the Criteria row for the City column, type London.
 * 7) On the View menu, click SQL View.

The SQL statement displayed is the first half of the union.
 * 1) 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.
 * 1) On the View menu, click SQL View. Select the entire SQL statement except the ending semicolon.
 * 2) Press CTRL+C to copy the selected text to the clipboard.
 * 3) Close the scratchpad query without saving it, and then switch back to the original query.
 * 4) 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.
 * 1) 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];

