MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword - Page 6
January 5, 2004
Now let's take a quick look at the TOP keyword used in conjunction with the optional PERCENT keyword.
36. Select SQL View using the View Selector button for a final time.
The SQL view editor appears.
37. Change the 19 after TOP to 10.
38. Add the word PERCENT immediately to the right of the 10 inserted above.
The PERCENT keyword in the above case instructs the query to return 10 percent of the rows (that is, approximately 40 rows, as we shall see).
39. Change the last word in the query (DESC in the ORDER BY clause) to ASC.
In this example, ASC directs that the ORDER of the range be from January, 1997 dates to December 1997 dates. TOP 10 PERCENT therefore will return the "top" ten percent of the rows from the January / beginning end of the range.
40. Save the query as ACC08-06
The query should now resemble that shown in Illustration 15.
41. Select Query --> Run from the main menu.
The resulting dataset appears as partially shown in Illustration 16.
We see that the first forty transactions for 1997 appear. A quick test of the accuracy of the result dataset can be performed by simply running the query again with the TOP 10 PERCENT keyword combination removed. The result set that returns indicates a total row count of approximately 398 rows. As we stated in the Syntax section above, the records returned using the TOP and PERCENT combination is composed of the stated numeric percent (ten, in this case) of the total rows (398 per the "proof" test), in whole rows (for a total of forty rows).
NOTE: Another way to see the total population that we have subjected to our keyword combination would be simply to substitute 100 (or "100 percent) in the place of the 10 in TOP 10 PERCENT. The result dataset again returns 398 records.
42. Close the query, without saving if you performed the test step, to return it to the TOP 10 PERCENT example we saved as ACC08-06.
Within the context of the practice example we have explored, we can easily see the usefulness of the TOP keyword, together with its optional "modifier" PERCENT, to help us to meet the business needs of information consumers. While we took a look at only a couple of possible uses, there are many scenarios where these keywords can be invaluable in helping us to meet our objectives. We will provide a further illustration of such an instance, where we can use TOP within a subquery scenario, in our next article, and perhaps in prospective articles over the months to come.
Conclusion and Summary
In this article, we introduced a keyword that will again take the stage in our next article, Access Query Techniques: Subqueries, Part II, which continues the exploration of subqueries we began in our previous lesson. We overviewed the TOP keyword, both with and without the optional PERCENT keyword, to examine its construction and operation independently from other distractions, to prepare us for its use in our next lesson. In Part II, we will create a subquery that leverages the keyword to illustrate more sophistication in the use of subqueries than we saw in our introduction to them in Part I.
We introduced the TOP keyword as it is used in Microsoft Jet SQL, and explored ways we can use it to meet illustrative business needs. To accomplish this, we examined the syntax surrounding the use of the TOP, and the optional PERCENT, keywords, then undertook illustrative practice exercises where we received hands-on exposure to the concepts. We then discussed the results datasets that we obtained in each of the practice examples.