Access 2002 Programming by Example

Using Subqueries to Unleash the Power Of Queries

Why not expand your possibilities using subqueries? There are certain scenarios that adopt well to the IN usage of a subquery. If you want to check values against another table, a subquery can be your answer. Think of it as a lookup table. Consider the following SQL example that uses two tables to retrieve all the products that have been sold in quantities over 70:

SELECT Products.ProductName 
FROM Products 
WHERE Products.ProductID 
In (SELECT ProductID FROM [Order Details] WHERE Quantity >=70); 

If you choose Design view on this query, notice that the IN clause becomes the criterion for the ProductID field in the Products table. Along the same lines, you might want to see records from two related ...