Subqueries

A query with an "In" criterion lets you enter a list of the values you want to test. This is useful but the list must be typed in when you design the query or entered by the user when they run it. A sub-query lets you use another query to create this list automatically each time that the user runs the query. It lets you answer a question like "Which customers have not placed an order?".

To do this you have to learn a lttle about SQL, the language behind Access queries.

SQL

SQL, the Structured Query Language, is complex and powerful but you can make a start with only two key words: SELECT and FROM.

The simplest query would be something like:

This query generates a list of the CustomerIDs from the Orders table - it's a list of the customers who have placed orders. If you put this simple query into a "Not In" criterion in the Query Designer then you will get a list of customers whose IDs do not appear in the Orders table - the customers who have not placed orders:

Writing SQL

If you need some assistance to write the SQL for the subquery then you can use a second instance of the Query Designer:

• Create a new query
• Add the Orders table
• Select the CustomerID field
• Run the query to check that it works
• Select SQL View from the View menu

This will open a window showing the SQL code behind the query:

This is very similar to the SQL in the example above. The only difference is that the Query Designer is being a little more precise. The "Orders.CustomerID" specifies that we want the CustomerID from the Orders table - even though that is the only table in this particular query. The final semicolon is optional in Access.

Edit this to bring the code onto one single line then copy and paste it into the "Not In" criterion of the original query. You can leave both the semicolon and the table names in place.