Use a subquery to define a field or define criteria for a field

You can use subqueries to, for example, test for the existence of some result from the subquery, find any values in the main query that are equal to, greater than, or less than values returned by the subquery, or to create subqueries within subqueries.

1. Create a new query.
   
   
2. In query Design view, add the fields you want to the design grid, including the fields you want to use the subquery for.
   
   
3. If you're using a subquery to define criteria for a field, type a SELECT statement in the Criteria cell of the field for which you want to set criteria. Place the SELECT statement in parentheses.

   

   If you're using a subquery to define a Field cell, type a SELECT statement enclosed in parentheses in a Field cell. After you leave the cell, Microsoft Access automatically inserts "Expr1:", "Expr2:", and so on, before the SELECT statement.

   Note   For more space in which to enter the SELECT statement while in a Field or Criteria cell, press SHIFT+F2 and enter the statement in the Zoom box.

   

   To rename the field, replace "Expr1:" with a field name — for example, "Cat:".

4. To see the results, click View on the toolbar.

Notes

• For more examples of subqueries used to define criteria, click .
  
  
• You can't use a field that you define with a subquery to group records.
  
  
• A subquery's SELECT statement can't define a union or crosstab query.