note that the number of records returned now matches the number of suppliers in
the Suppliers table. A quick examination of the previous dataset,
whereby the total Spend value represented the total for each supplier-product
combination, reveals that our totals do, indeed, appear to be correct as a
few sample additions will bear out. We are now ready to rank the vendors, to
meet precisely the business requirement of our target audience.
Click the New
button atop the Database window.
The New Query
the Design View option is selected, click OK.
The Select Query
dialog, with the Show Table dialog appearing in front, appears as before.
on the Show Table dialog.
Table dialog closes, leaving only the Select Query dialog present.
Select SQL View using
the View Selector button once again.
The SQL view editor appears.
following new query into the editor:
ACC0704 AS CoSpendTotal
ACC0704.Spend <= CoSpendTotal.Spend)
Save the query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
Illustration 15: The Query / Subquery Results Dataset
In this second query, another simple SELECT query, we
have embedded our initial query (which we constructed in steps one through four
above), as a subquery. This arrangement induces a scenario whereby the primary
query executes the nested subquery for each row of the result
set. The effect is that it performs a count of the rows whose Spend
value exceeds or equals the Spend value of the current row, by
performing a comparison between the original query and a clone of itself.
The row-by-row operation of the process might make our
example inefficient within a "real world" realm involving a huge
table, but our purpose here is simply to illustrate the operation of a subquery
in reaching a reporting objective, and this example shows particularly well why
such a construct lends itself to ranking operations.
the query is saved as ACC0705, select File --> Close.
the auspices of the practice example we have explored, we can easily see the
utility of subqueries in returning datasets to meet a business need. While the
overhead involved in the subquery approach might render it suboptimal in some
environments, and while various other considerations might come into play in
deciding whether to deploy subquery functionality in our production
environments, there are many, many scenarios where subqueries can be invaluable
in helping us to meet our objectives. We will provide further illustrations of
such instances, particularly where subqueries can be mingled with other
techniques and functions to achieve desired ends, in prospective articles.
this lesson we introduced subqueries within the context of MS Access, and
explored their creation and use in rendering a result dataset to meet an
illustrative business need. To accomplish this, we examined the syntax
surrounding the use of an example subquery, and then began an illustrative,
hands-on example of the use of a subquery in a multi-step practice exercise. We
first built the initial SELECT query involved, to illustrate the
underlying operation and its effects upon the data. After evolving the initial
query, we created a second query, within which we nested the first, to
demonstrate the operation of a subquery in our practice exercise. Finally, we
briefly discussed various aspects of the results datasets that we obtained in
each step of the practice examples.
See All Articles by Columnist William E. Pearson, III