MS Access for the Business Environment: Access Query Techniques: Subqueries, Part II - Page 5
February 2, 2004
Our next step will be to build an independent SELECT query, which will be destined to become the primary query that houses the SELECT query with the TOP statement as we left it at our last step. We will first create this SELECT query to give us a layout to meet precisely the business requirements of the information consumers that requested it; we will then tweak it for any refinements we deem appropriate.
20. Close query ACC09-02.
We are returned to the view of existing queries.
21. Click the New button atop the Database window
The New Query dialog appears.
22. 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.
23. Click Close on the Show Table dialog.
The Show Table dialog closes, leaving only the Select Query dialog present.
24. Select SQL View using the View Selector button once again.
The SQL view editor appears.
25. Type the following new query into the editor:
SELECT CStr(Employees.EmployeeID)+" - "+LastName AS Colleague, OrderDate FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID ORDER BY Employees.EmployeeID ASC, Orders.OrderDate DESC
26. Save the query as ACC09-03.
27. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 10.
While we appear to have constructed a query that generates the layout required to meet the consumers' needs, we obviously have returned the complete population of customer orders by Colleague and Order Date, there being no filtering whatsoever in place at this stage.
Next, we will begin the marriage of the two SELECT queries, embedding the query with the TOP keyword we constructed first into the primary query we have constructed and saved as ACC09-03.
28. Save the query again as ACC09-04, to prevent any accidental damage to ACC09-03.
29. Select SQL View using the View Selector button once again.
The SQL view editor appears.
30. Type the following new query into the editor:[lbc]
31. Select SQL View using the View Selector button once again.
The SQL view editor appears.
32. Type the following into the editor, between the
ON Employees.EmployeeID = Orders.EmployeeID
clause and the
ORDER BY Employees.EmployeeID ASC, Orders.OrderDate DESC
WHERE ((Orders.OrderDate) IN (SELECT TOP 5 OrderDate FROM Orders WHERE Orders.EmployeeID = Employees.EmployeeID AND Orders.OrderDate < #31-Dec-1997# ORDER BY OrderDate DESC))
The query should now resemble that shown in Illustration 11.
33. Save the query once again as ACC09-04.
34. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 12.
In this, our second SELECT query, we have embedded our initial query (which we constructed in steps 12 through 17 above), as a subquery. This arrangement induces a scenario whereby the primary query executes the nested subquery, applying it as a filter against the primary result dataset. The presence of the subquery within the IN clause means that the dataset returned from the subquery is compared to the dataset from the primary SELECT statement to determine the dataset that is returned. Our addition of the join in the WHERE clause, which joins a field from the nested subquery with a field within the primary query, allows our construct to return the appropriate, differing results for each of the employees returned by the primary query.
As we discussed in Part I, the mechanics of the process that underlies our final query might make a construct similar to our example inefficient, within a "real world" scenario involving a huge table. Our purpose here is simply to illustrate the operation of a subquery in reaching a reporting objective. In this case, the subquery incorporates the TOP keyword as a part of its operation, to give us the "top" data relating to the members of a much larger population. While we used TOP in our example to narrow a result dataset to the latest five dates' data for the employee set returned by the primary SELECT query, we might just as well have used TOP to select a specified top (or bottom) number of occurrences based upon another value.
35. Ensuring that the query is saved as ACC09-04, select File --> Close.
Within the practice examples we have explored in Parts I and II of this lesson, we can get a good idea of the utility of subqueries in returning datasets to meet business needs. While various considerations (such as the overhead involved in the operation of a subquery construct) come into play in deciding whether to deploy subquery functionality in our production environments, there are many scenarios where they can be invaluable in helping us to support information consumers in our organizations.
With this lesson, we continued the examination of MS Access subqueries that we began with Part I, extending our exploration of their construction and use to meet additional business needs. We examined the syntax surrounding the use of an example subquery, reviewed the expected results from the same example and then began an illustrative, hands-on example of the use of a subquery in a multi-step, practice exercise.
We began with the construction of a relatively basic SELECT query, to illustrate the underlying operation and its effects upon the data. After evolving the initial query, we created a second SELECT query, within which we then nested the initial query, to demonstrate the operation of a subquery, containing the TOP keyword, in our practice exercise. Finally, we briefly discussed various aspects of the results datasets that we obtained at relevant points throughout the practice example.