Today’s guest post come’s from Mark Davis, a Access MVP. You can learn more about him at http://www.linkedin.com/in/markgerarddavis

The concept of a subquery itself is fairly straightforward: have a query within a query. However, there are various applications of the subquery. Most frequently, a subquery is used in a filtering context:
SELECT a FROM n WHERE a NOT IN (SELECT a FROM x)
However, this is only the tip of the iceberg of their usefulness. Subqueries can also be used to create a ‘temporary view’ within your query construct. Let me give an example. Suppose that we have a table, tblTimeClock, consisting of an employee ID, a dateWorked column, and a timeLogged column of type double. When an employee clocks out for a day, their id and the total time they worked for the day is logged here. Now, we want to retrieve for a given day the employees that worked, how long each employee worked, and the total hours all employees logged for the day. Without subqueries, we can do one or the other, but not both. To retrieve the time each employee worked for the day, we would write something like:
SELECT employeeID, Sum(timeLogged) AS totEmpTime
FROM tblTimeClock
WHERE dateWorked=#11/13/2009#
GROUP BY employeeID
This would return us the time logged by each employee. Now, to get the total time logged, we could do . . .
SELECT employeeID, Sum(timeLogged) AS totEmpTime, tTime.totTime
FROM tblTimeClock,
[SELECT Sum(timeLogged) AS totTime
FROM tblTimeClock
WHERE dateWorked=#11/13/2009#].
AS tTime
WHERE dateWorked=#11/13/2009#
GROUP BY employeeID
As the subquery only returns one value, it is a scalar subquery. That subquery is aliased as a table, and can be treated as such by referencing its alias, and then the desired column name.

NOTE: Pay attention to the period after the closing bracket. Omitting this period will cause Access (JET – not yet fully tested in ACE) to not properly recognize the subquery. Also, there can be no brackets within the defining brackets, as this will cause the query to fail. So this means the field names in the table(s) must contain no spaces.

In a more complex but practical example, say we want to retrieve all dry chemical lots produced in November 2009, and their moistures, if available. Building a query as such:
SELECT Lot.Lot_Number, rptQuality.cValu
FROM LOT LEFT JOIN rptQuality ON Lot.Lot_Number = rptQuality.lotnum
WHERE rptQuality.rstid=10 AND Lot.Lot_Completion_Date>=#11/1/2009 12:0:0 AM# AND Lot.Lot_Completion_Date<=#11/30/2009 11:59:59 PM# AND Lot.Hydro=’d’
 

Will only return to us the lot numbers and moistures of those lots that have been tested, regardless of the left join.  This is because we are filtering our result set by a value of that in the left-joined table (rstid=10), so we will only retrieve those lots that actually have moistures.  However, there may be several dozen lots that have been produced but not yet tested, and we do not want to exclude these lots from our query.  An appropriate solution is outlined below using a non-scalar subquery:
SELECT Lot.Lot_Number, rptQual.cValu
FROM LOT LEFT JOIN [SELECT rptQuality.lotnum, rptQuality.cValu FROM rptQuality WHERE rptQuality.rstid=10]. AS rptQual ON Lot.Lot_Number = rptQual.lotnum
WHERE Lot.Lot_Completion_Date>=#11/1/2009 12:0:0 AM# AND Lot.Lot_Completion_Date<=#11/30/2009 11:59:59 PM# AND Lot.Hydro=’d’
 

This query pre-filters our results down to just those lots with moisture values. Then, the left join is applied. With no additional filters on the left-joined table, our query will correctly return ALL produced dry lots in November, and show any relevant moistures.

Subqueries used in this manner may be treated exactly like any other table or query, where one can aggregate data and perform any other transformations as necessary.

With Access 97 (newer versions untested), there are some limitations to the subquery. First, a subquery cannot be nested within a subquery. In extremely complex cases, you will have to either store the results of one of the subqueries in the back-end .mdb as a view or temp table, or figure out a way to create the complex join syntax to make it work properly. Also, Access 97 does not like the use of [] to encapsulate relative field references. I.E. [SELECT [Lot_Number] FROM Lot]. AS LtNum will error Access in a heartbeat. You MUST use absolute field references as such: [SELECT Lot.Lot_Number FROM Lot]. AS LtNum. To summarize you cannot use [ or ] within your subquery, or Access will produce errors. Meaning, the field names in the table(s) must contain no spaces.

For more information on subqueries and scalar subqueries, please visit the following resource(s):
http://www.blueclaw-db.com/accessquerysql/scalar_subquery.htm