ACC: Adding a Parameter to a Union Query (132135)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q132135

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

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.

NOTE: In Microsoft Access 2.0, a similar query called Union Query can be found in the sample database NWIND.MDB.

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;

The SQL of Union Query in Microsoft Access 2.0 is as follows: 
   SELECT [Company Name], [City]
   FROM [Suppliers]
   WHERE Country = "Brazil"
   UNION SELECT [Company Name], [City]
   FROM [Customers]
   WHERE Country = "Brazil";

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 (or NWIND.MDB in version 2.0). You may want to back up the Northwind.mdb file, or perform these steps on a copy of the Northwind database.
  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
  2. Open the Customers and Suppliers By City query (or Union Query in Microsoft Access 2.0) in Design view.
  3. Change the query's SQL so that it looks like the following:

    In Microsoft Access 7.0 and 97: 
          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;

    In Microsoft Access 2.0: 
          SELECT [Company Name], [City]
      FROM [Suppliers]
      WHERE Country = "Brazil" and City = [Enter City]
      UNION SELECT [Company Name], [City]
      FROM [Customers]
      WHERE Country = "Brazil" and City = [Enter City];

  4. Save the query and close it.
  5. 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, search for "union queries," and then "Combine data in fields from two or more tables using a union query" using the Microsoft Access 97 Help Index.
Modification Type:MajorLast Reviewed:5/7/2003
Keywords:kbhowto kbusage KB132135
©2002 Microsoft Corporation. All rights reserved.