How to Create a Subquery in Microsoft Access

Microsoft Access, Microsoft Office

Just as there can be subforms and subreports within an Access form or report, there can be a subquery within a query. As the term implies, a “query within a query” allows the main query to be more specific, or complex. It sometimes requires a little bit of Structured Query Language, or SQL. But this is not a problem. Access is a “shell” over SQL as Windows was a shell over DOS, and the two get along fine.

A typical example would be setting up a subquery based on text information, to allow more flexibility with the use of number fields in a database. After opening the DB, we bring up the query window—better to do this in Design view—and add our basic query info.

We can then either switch to SQL view, which some SQL users prefer, or right-click in the appropriate column and row. We then use the Zoom command to write the subquery.

Here we want to ask which products are equal to or less in price than Colby cheese. We want to base the question, though, on the name of the product, NOT the price itself. This is the key.

So in the UnitPrice column of the query (through the Zoom box), we tell the subquery to select (and show) the products from the table (of Products) where unit prices are less than or equal to that of (the product name) Colby cheese. (Note the SQL keywords.) What usually causes a little confusion is that the “less than or equal to” operator comes before anything else. But the nature of comparisons in Access, as well as in SQL, often requires this. And since the comparison is numerical for all that we’re writing a “verbal” formula here, it comes with the territory. Once we know about it, it’s pretty straightforward.

One point not everyone knows, though. If something is edited in the query results, we’re editing the actual data, not a copy. So a query (along with a subquery) can be used to find and correct potentially erroneous or out-of-date info. (One techno-term I’ve heard is “reflexive correction”.)

To learn more about this program, check out our Microsoft Access classes here.