ACC: How to Create and Use Subqueries

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

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

Microsoft Access 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.

MORE INFORMATION

To create a subquery, enter a valid SQL SELECT statement in the Criteria cell of the field whose value will be 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);

The following example demonstrates how to create a subquery:

1. Open the sample Northwind.mdb database (or NWIND.MDB in version 2.0).

2. Create a new query and add the Products table.

3. Add the ProductName and UnitPrice fields to the query grid.

   NOTE: In version 2.0, there is a space in Product Name and in Unit Price.

4. Create the subquery by entering the following SQL SELECT statement in the Criteria cell of the UnitPrice field.

   NOTE: In the following statement, an underscore (_) is used as a line- continuation character. Remove the underscore from the end of the line when re-creating this statement.

         <=(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, search the Help Index for "Subqueries," or ask the Microsoft Access 97 Office Assistant.