ACC2000: Adding a Parameter to a Union Query

ID: Q208815

The information in this article applies to:

Microsoft Access 2000

Advanced: Requires expert coding, interoperability, and multiuser skills.

SUMMARY

An easy way to prompt a user for a search criteria in a query is to create a parameter query. However, to use a parameter in a union query, you must follow some specific rules. This article uses the Customers and Suppliers By City query in the sample database Northwind.mdb to describe how to add a parameter to a union query.

MORE INFORMATION

The Customers and Suppliers By City union query selects records from both the Customers and Suppliers tables. The SQL of this query is as follows:


SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City, CompanyName;

To add a parameter to the union query, you must duplicate the parameter in each SELECT clause in the query. To add a parameter (in this case a city) to the query, follow these steps.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

Open the sample database Northwind.mdb.

Open the Customers and Suppliers By City query in Design view.

Change the query's SQL so that it looks like the following:


SELECT City, CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
WHERE Country = "Brazil" and City = [Enter City]
UNION SELECT City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
WHERE Country = "Brazil" and City = [Enter City]
ORDER BY City, CompanyName;

Save the query and close it.

Open the query in Datasheet view and type Rio de Janeiro in the Enter City dialog box. Note that only the Customers and Suppliers from Rio de Janeiro are displayed.

REFERENCES

For more information about union queries, click Microsoft Access Help on the Help menu, type "union queries" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words:

Keywords : kbusage kbdta QryUnion
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto