ACC2000: How to Create and Use Subqueries (209066)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q209066
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

SUMMARY

Microsoft Access 2000 supports the use of subqueries. A subquery is a select query inside another select or action query that acts as a criterion for a field. A subquery can refer to a table that is part of the current query, or to a table that is not part of the current query. Union and crosstab queries are not supported as subqueries.

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

207626 ACC2000: Access 2000 Sample Queries Available in Download Center

MORE INFORMATION

To create a subquery, type a valid SQL SELECT statement in the Criteria cell of the field whose value is being compared with the value selected in the subquery. The field that the criterion is specified for and the field that is being selected in the subquery must be of the same data type. You can also have a subquery in the Field row of the query grid, although it is not recommended.

A correlated subquery is one in which a column from a table specified in the FROM clause of the main query is used in the WHERE clause of the subquery, as demonstrated in the following example:

SELECT * FROM Table1 WHERE FirstName IN (SELECT FirstName FROM Table2 WHERE Table2.LastName=Table1.LastName);

Another way to tell if a subquery is a correlated subquery is if the subquery statement cannot be used by itself as a separate query. In the example above, Access would not be able to identify Table1.LastName if the second SELECT statement were used by itself in a separate query.
To create a subquery, follow these steps:

  1. Open the sample Northwind.mdb database.
  2. Create a new query, and then add the Products table.
  3. Add the ProductName and UnitPrice fields to the query grid.
  4. Create the subquery by typing the following SQL SELECT statement in the Criteria cell of the UnitPrice field:

    <=(SELECT [UnitPrice] FROM [Products] WHERE [ProductName]= "Filo Mix")

  5. Run the query. Note that the query returns the products whose unit prices are less than or equal to the unit price for Filo Mix.

REFERENCES

For more information about creating subqueries, click Microsoft Access Help on the Help menu, type use a subquery to define a field or define criteria for a field in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbhowto kbinfo kbusage KB209066
©2004 Microsoft Corporation. All rights reserved.