Go to Top

Subquery and the Scalar Query

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:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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:

This would return us the time logged by each employee. Now, to get the total time logged, we could do . . .

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:

 

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:

 

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

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

4 Responses to "Subquery and the Scalar Query"

  • Mark Burns
    May 7, 2012 - 8:17 pm Reply

    Also, please be aware that this is a little-used and little tested JET SQL capability, and based upon my limited use of these syntax versions, the “() As MyName ” subquery syntax CAN BE somewhat fragile and embedded function calls can easily confuse this syntax’s evaluation.

  • Mark Burns
    May 7, 2012 - 8:13 pm Reply

    Actually, there are TWO JET SQL syntaxes for subqueries.

    The first, which Mark Davis reference here is:
    [].
    The second format is:
    {Outer SQL Query Start} ({SQL Subquery here} ) AS MySubQ {outer SQL Continues here with references to MySubQ.AnExposedMySubQFieldNameHere as needed}

    However, be aware that the Access query editor doesn’t support the second format, but Jet itself does. In fact, the Query editor has been known to take a query in the second format and convert it to the first format (by changing the query view from SQL to any other format and going back to SQL). This second format DOES support the use of bracketed field names in subquery text, and this bracketed fieldname support gets broken down when the query is then manipulated in the query editor and the format reverted to the […]. format.

    I don’t have the links to related MSDN content handy at the moment, but this is documented JET behavior.

  • Giorgio
    May 7, 2012 - 3:02 pm Reply

    Hi Juan, is this correct:
    Lot.Lot_Completion_Date>=#11/1/2009 12:0:0 AM# AND Lot.Lot_Completion_Date=#11/1/2009 12:0:0 AM# AND Lot.Lot_Completion_Date
    ?

    • Cybercow
      May 7, 2012 - 9:29 pm Reply

      Hi Giorgio,

      Thanks for catching that. No, it is not correct, but if you refresh the page you see that the SQL statements have been corrected and a missing bit of text has been replaced. Thanks for the head’s up.

      Mark Davis

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]