Top Values Property
It seems that modern societies care only about the biggest and the best. If a company can't have the best-selling brand in a market, it often doesn't want to be in it at all. The weekly box office totals from the movie industry focus only on the top ten moneymakers. On the PGA tour, scratch golfers who finish 58th on Sunday might as well have stayed in bed.
The Top Values property is well suited to the needs of our times. It finds the highest values and the lowest. Access determines the "top" records based on the sorting criteria you specify. It can be set in either absolute or percentage terms, so you can use it to find the top 1% of this or the top five of that. In fact, you can limit the records found to any number or percentage in either direction you choose. In a column of baseball batting averages, for example, you could find the top 75 hitting artists or the 20% at the bottom who can be counted on to strike out with the bases loaded.
Here are three things to remember when using the Top Values property:
Find a Top (or Bottom) Number of Records
Let's do a few examples to see how Top Values works. Suppose you want to find the five items in Nifty Lions's inventory that have the highest price.
If there are records with duplicate values in the last slot of eligible top values, Access retrieves all those records. Here's an example: Let's say you want to find the five products with the most units in stock. In the UnitsInStock field, the products with the most units have values of 12, 11, 10, 9, 8, 8, and 4. Access retrieves both records with 8 units (that is, the products in the fifth, or last, position from the top), but not the record with 4 units. So Access retrieves six records instead of five. Thus, you might occasionally get more records than you specify.
Find a Top (or Bottom) Percentage of Records
Suppose you want to find the products with the lowest 70% of all prices. In other words, the 30% of products with the highest prices will not be included.
Let me re-emphasize that, unlike a filter that needs to be applied, a saved Top Values property is in force when you open and run the query. Because you just created the query, that's not an issue. But I hope you'll agree that my admonition about resetting the Top Values property to All before exiting the query is worth considering. You can always set the Top Values property anew when you open the query. If you do want to save the Top Values property, consider including that fact in the query name (as in TopFiveSellers).
As a query property, Top Values is on the Query property sheet and can be specified there as well. To open the Query property sheet, right-click in any open area of the upper pane of the Query Design Window and choose Properties.