MS Access for the Business Environment: Access Query Techniques: Subqueries, Part II - Page 4
February 2, 2004
As I mentioned in Part I, subqueries as a group require special attention, and, while we can craft many of them within the query design grid, situations requiring subqueries are best handled through the use of direct SQL. This is also the best environment from which to gain a basic understanding of SQL, as many of us become aware with our first challenges to meet real business needs with MS Access. Our practice efforts in this lesson, like the previous two lessons, will be undertaken using direct SQL throughout.
We will begin the steps of the exercise by composing a simple SELECT query, and then we will gain an understanding of the dataset that it returns; our query will focus solely on the Orders table in its first step.
12. Type the following basic SELECT query into the editor:
SELECT TOP 5 OrderDate FROM Orders ORDER BY OrderDate DESC
We can run the query at this stage by selecting Query --> Run from the main menu.
13. Save the query as ACC09-01.
14. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 6.
Before going further, let's investigate an apparent issue that appears. We have asked for the "top" five (or five most recent in the table) order dates, but we see eight records in the result dataset. As we illustrated in our last lesson, Using the TOP Keyword, the reason for what appears to be a deviation from the expected results is simply a manifestation of the standard behavior of the TOP keyword in the event of a "tie." When equal values are present in the range from which we are selecting the "top" records, all records that have the equal value are returned. In the immediate case, we asked for the five most recent Order Dates in the table, sorted in descending order. Starting from the "top" of this list, the "top four" records contain the same date, 06-May-1998. When we move to the next date, the "fifth from the top," we see a new date, 05-May-1998. However, 05-May-1998 occurs for the next four records, until, at the ninth record, the Order Date becomes 05-May-1998. Because TOP treats "ties" as equal, all records containing 05-May-1998 (a total of four) are returned. This results in the eight records that we see in the result dataset.
To prove this concept, we can open the Orders table, sort the rows by Order Date descending (by highlighting the column heading for Order Date and clicking the downward arrow button on the toolbar), then examine the "top" rows in the newly sorted table. The resulting Orders table view appears as partially shown in Illustration 7, where the "tied" rows are indicated by the bracket I have added in the illustration.
We have assembled the basic SELECT query, and run it to obtain the results dataset, examining our results for accuracy. Our objective in doing so is to begin setup of the first of two independent SELECT queries. The second query will "enclose" the first as a subquery, as we shall see. In this way, we can witness the subquery in action, after having obtained a familiarity for the underlying data involved.
We noted that the above query delivers a record set containing eight dates, for reasons we have explained. However, a review of the requirements of the information consumers indicates an adjustment to our query is in order: We need to limit the latest five dates selected to the year 1997.
15. Ensure that the query is saved as ACC09-01.
16. Shift back to SQL view, once again.
17. Add the following, immediately before the ORDER BY clause of ACC0901:
WHERE Orders.OrderDate < #01-Jan-1998#
18. Save the query as ACC09-02.
The query should now resemble that shown in Illustration 8.
19. Select Query --> Run from the main menu.
The resulting dataset appears as shown in Illustration 9.
We can easily verify accuracy and completeness of the returned dataset through a quick look at the Orders table, as before, or via an independent query. This time, the dates played into our TOP statement well, with no "ties" in visually inconvenient places.