Returning the top n values

Ever wanted to return a specified number of the highest values in a field? Well, as with so many things in Access there are two ways of doing this. At least they both boil down to the same thing.

The first way is the easiest if you don't like to muddy your hands with SQL too much. Open the query in design view and bring up the Properties window. Set the TopValues parameter - this can take an integer value (5, 10, 25, etc) or a percentage (10%, 20%, and so on) - in turn, these values constrain the data returned by the query. Then sort the field you want to return the top values of in descending order. Easy, eh? But I can almost hear you asking, what about a “bottom values” property? Well there's no such thing... instead, simply sort the field you want to return the bottom values of in ascending order and then set the TopValues value as described above.

The other way of doing this is to get down and dirty with some SQL, and essentially do manually what the TopValues property/sort order combo does for you automatically. Here's an example - suppose you want the names of the top 10 students from the class of 1992. Here's the SQL:

SELECT TOP 10
Forename, Surname
FROM tblStudents
WHERE GradYear=1992
ORDER BY FinalGrade DESC;

Here's another example - this time we're going to pull off the names of the bottom 10% of students from the same year. Here's the SQL - note the use of the PERCENT keyword and the change in sort order:

SELECT TOP 10 PERCENT
Forename, Surname
FROM tblStudents
WHERE GradYear=1992
ORDER BY FinalGrade ASC;

Too easy, right? But there are a few important things to note here though. If you do not define a sort order (the ORDER BY clause if you're using raw SQL), the query will return an arbitrary set of n records which is not what you want at all! Secondly, the TOP predicate does not choose between equal values. In the first SQL statement above, if the twenty-fifth and twenty-sixth highest students have the same final grade the query will return 26 records. And finally, the value that follows TOP must be an unsigned integer data type.