Top Values Property

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:

• The field you sort on is the one that Access uses to find the top or bottom values. If you sort on two fields, make sure the sort you want for top values is the column farthest left in Query Design view.

• The required sort order is sometimes opposite to your initial inclination of what's needed. In a list of prices, for example, use a descending sort to find the highest prices. (That advice is applicable for all sorts, but it has special relevance here.)

• Most important (even if most mundane), remember to reset the Top Values property setting to All when you're done with it. The Top Values setting will be saved along with the query, and it will be in force when you open and run it. You can easily wind up viewing just a small fraction of the records you need.

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.

1.	Select the tblMerchandise table in the Database window. Choose Insert, Query and, with Design View selected, click OK. Maximize the window.
2.	Save the query as qryTopMerchPrices.
3.	Add the MerchName, MerchDescription, PurchasePrice, and UnitsInStock fields to the design grid.
4.	Type d in the Sort row of the PurchasePrice field for a descending sort. To show the most expensive products first, use a descending sort to sort the PurchPrice field from highest to lowest.
5.	Click View, briefly review the fields and records, and return to Design view.
6.	On the Query Design toolbar, click the drop-down arrow next to All and select 5 (see Figure 8.5). Figure 8.5. The Top Values property is used to find numbers at the extreme and not-so-extreme.
7.	Click View to go to Datasheet view. The records are sorted by purchase price, with the most expensive products listed first.
8.	Click View to return to Design view. Open the drop-down list for the Top Values property and change the Top Values property to All.

NOTE

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.

1.	In the Sort row of the PurchasePrice field of qryTopMerchPrices, open the drop-down list and choose Ascending. An ascending sort lists the lowest-priced items first and the highest-priced items last.
2.	Highlight All in the Top Values box. Type 70%. Besides the few choices in the drop-down list, you can type numbers and percentages directly into the Top Values box for the specific fraction you want.
3.	Click View to switch to Datasheet view. Products in the lowest 70% of all records are displayed.
4.	Close the query and save your changes.
5.	In the Queries section of the Database window, double-click qryTopMerchPrices to open it. The records are still sorted by ascending price, and only the top 70% are displayed.

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).

NOTE

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.