MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1 - Page 6
December 1, 2003
We 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.
32. Click the New button atop the Database window.
The New Query dialog appears.
33. Ensuring that the Design View option is selected, click OK.
The Select Query dialog, with the Show Table dialog appearing in front, appears as before.
34. Click Close on the Show Table dialog.
The Show Table dialog closes, leaving only the Select Query dialog present.
35. Select SQL View using the View Selector button once again.
The SQL view editor appears.
36. Type the following new query into the editor:
SELECT CompanyName, Spend, (SELECT COUNT(*) FROM ACC0704 AS CoSpendTotal WHERE ACC0704.Spend <= CoSpendTotal.Spend) AS Rank FROM ACC0704 ORDER BY Spend DESC
37. Save the query as ACC0705.
38. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 15.
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.
39. Ensuring that the query is saved as ACC0705, select File --> Close.
Under 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.
With 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.