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:
-
Open the sample Northwind.mdb database.
-
Create a new query, and then add the Products table.
-
Add the ProductName and UnitPrice fields to the query grid.
-
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")
-
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.