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.
Illustration 15: The SELECT Query with TOP Modifications and
PERCENT Add
41.
Select Query
--> Run from the main menu.
The resulting dataset appears as partially shown in Illustration
16.
Illustration 16: Results of Modification of the TOP
Keyword
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.
»
See All Articles by Columnist William E. Pearson, III
» 
Comment and Contribute
