Microsoft Access - Returning A Subset Of Top Or Bottom Values In A Query (Access 97/2000/2002/2003)


It's easy to create a query that returns results based on a specified number of values found in a field or a percentage of records based on a field value. To do so, you use a query's Top Values property. For instance, this property can be used to list only records where a field contains the top 10 values found in the underlying recordset. Or, you can return records where a field value is in the lowest 25 percent of all the records- values.


Let's say you have a query that returns a list of products that has a descending sort order applied to the quantity sold field. When you run the query, the records with the highest number sold are at the top. However, you want just the top 25 percent of the sorted results to show in the query result. To do this, open the query in Design view. Then, use the Top Values dropdown list on the Query Design toolbar to specify how many records to return. If you want the query to return a number or percentage that doesn't appear in the dropdown list, simply enter your own custom value in the associated text box.


Remember that the sort order applied to the query determines which records are the top values. If you have a descending sort applied, the top values are the largest values. With an ascending sort applied, your top values are the lowest values.

Go back