ACC: How to Create and Use Subqueries

ID: Q114678

The information in this article applies to:

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 critera 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: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

Q182568 ACC97: Microsoft Access 97 Sample Queries Available on MSL

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);

Another way to tell if a subquery is a correlated subquery is that if the subquery statement couldn't be used by itself as a separate query. In the example above it wouldn't know what Table1.LastName was.

The following example demonstrates how to create a subquery:

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

Create a new query and add the Products table.

Add the ProductName and UnitPrice fields to the query grid.

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

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") 
```

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.

Additional query words: sub-select


Keywords          : kbusage QryOthr 
Version           : 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: April 3, 1999