SQL Subqueries in Microsoft Access

A query with an " In " criterion lets you enter a list of the values you want and retrieve records which match any one of them. This is a useful feature of SQL in Microsoft Access but the list must be typed in when you design the query or entered by the user when they run it. A sub-query gives more flexibility. It lets you build this list automatically by running another SQL query each time that the user runs the main query. It lets you answer a question like "Which customers have not placed an order?".

To do this you have to learn a little 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:

This query does what you'd expect it to do from a simple reading of the command "Select customerID from Orders". It selects the CustomerIDs from the Orders table and gives you a list of the customers who have placed orders.

You could copy the values from this list into the Query Designer as the criterion in a query which gets names and details from the Customer table. If you used "Not In" then the query would give you the customers who weren't in the list of customers who had placed orders. A sub-query makes life easier. You can use the SQL statement instead of the list and let Access generate the list when the user runs the query.

Type this simple query into a "Not In" criterion in the Query Designer:

Writing SQL

If you need some assistance to write the SQL for the subquery then you can cheat. Open the Access 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 that the Query Designer has generated automatically:

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. There's only the one table in this particular query but Access doesn't know whether you might be planning to add another table later. 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.