MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword - Page 5
January 5, 2004
Our next step will be to limit the transactions to the year 1997. Our results dataset currently displays the combined Order information for all years in the table. We can restrict the data to the year under examination with the addition of a simple WHERE clause, as we shall see.
23. Shift back to SQL view, once again.
24. Add the following WHERE clause between the existing FROM and GROUP BY clauses:
WHERE ShippedDate Between #1/1/1997# And #12/31/1997#
This will restrict the records returned to those representing transactions with Shipped Dates that lie between January 1st and December 31st, inclusive.
25. Save the query as ACC08-03.
The query should now resemble that shown in Illustration 10.
Click for larger image
26. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 11.
We can see that the transactions now appear to be limited to those that occurred in 1997, a circumstance that is also confirmed by the fact that the total number of records has again decreased markedly, this time to just under 400.
We are now ready to insert the TOP keyword to drive the generation of the "top" twenty records. Recall that the ORDER BY clause actually makes this work. We are simply going to order our existing result dataset by Shipped Date, descending, so that we obtain the records by date, most recent to oldest. Then, when we insert the TOP keyword, we are essentially asking for the "top twenty" of that list (which is ordered, again, most recent to oldest). We are thus saying "give me the twenty most recent" - or the twenty last - transactions that occurred in 1997.
27. Shift back to SQL view, once again.
28. Add the following to the SELECT clause, to the immediate right of the word SELECT:
29. Add the following ORDER BY clause to the end of the query (be sure to remove the semicolon (";") that ends the line above, if it is present).
ORDER BY Orders.ShippedDate DESC;
30. Save the query as ACC08-04.
The query should now resemble that shown in Illustration 12.
31. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 13.
We immediately see the effects of the TOP keyword, together with the ORDER BY clause through which it is driven. We see, as well, how we can use TOP with dates, as well as the more intuitive number data types, to deliver a results dataset from a range of dated transactions. An examination of the number of records in the dataset, however, presents a perplexing phenomenon: the counter indicates "22," instead of the twenty records we might have expected.
The reason for the apparent deviation from the expected results is simply a manifestation of the 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.
To prove this concept, take the following step.
32. Select SQL View using the View Selector button once again.
The Query appears.
33. Change the 20 after TOP to 19.
34. Save the query as ACC08-05.
35. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 14, and is limited to nineteen rows, because the nineteenth Shipped Date from the end of 1997 has only one occurrence, 16-Dec-1997.