Subqueries In Access
You don’t have to be a database guru to use
SQL. While you’re much more likely to run into SQL using Microsoft Access or
SQL Server, you can actually issue database queries from any of the Microsoft
Office applications using VBA and the ADO library.
Access is a great way to
start learning SQL. Use the built-in Query Wizard to help you mine the data you
want, then click on the drop-down icon at the top left of the Query Window. If
you’re an Access user, you’ve already seen the Datasheet View button and the
Design View button. They appear to toggle one to the other but in fact, there’s
a third option: SQL View.
You can use this view to see how queries are
constructed in Access, or if you’re more comfortable with SQL you can see how
SQL gets ‘translated’ by the Access Query Builder.
A subquery can be handled
several different ways in Access. The first way is to create separate queries
and then use one as the source for the other. For instance, you may want to
write a report showing sales for the first quarter, the second quarter, and both
quarters. The date ranges are mutually exclusive and cannot be placed in the
same query. A simple answer is to write one query for the first quarter, one for
the second, and one for both. Finally, write a “master” query that pulls all
the data together. The quarterly queries act like subqueries. They run first and
the master runs after. It’s like sub-assemblies being pieced together on an
assembly line.
You can use SQL to write a subquery as part of the same query
object. In this case, you would see only one query in the Access query window. A
seemingly simple query that requires a subquery is “Show me a list of all
products more expensive than average.” Seems simple enough but we need to
figure out what average is first, then run the query armed with this value. To
find the average unit price of products in the Northwind.mdb database (a
standard part of the Office install) we’d use:
SELECT AVG(UnitPrice) FROM Products
This query returns a single number after some number crunching. The
SQL for listing all products over, say, $10.00 would look like
SELECT
ProductName, UnitPrice FROM Products WHERE UnitPrice > 10
If we combine the two into a query-subquery we get
SELECT ProductName, UnitPrice FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products)
You can paste this
directly into the SQL View of a new query and then switch to Design View. You’ll
see that Access has put the subquery in the criteria row of the UnitPrice field.