Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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:

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

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores