TopValues Property

You can use the TopValues property to return a specified number of records or a percentage of records that meet the criteria you specify. For example, you might want to return the top 10 values or the top 25 percent of all values in a field.

 Note   The TopValues property applies only to append (append query: An action query that adds the records in a query's result set to the end of an existing table.), make-table (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in that table by copying records from an existing table or query result.), and select (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) queries.


The TopValues property setting is an Integer (Integer data type: A fundamental data type that holds integers. An Integer variable is stored as a 16-bit (2-byte) number ranging in value from -32,768 to 32,767.) value representing the exact number of values to return or a number followed by a percent sign (%) representing the percentage of records to return. For example, to return the top 10 values, set the TopValues property to 10; to return the top 10 percent of values, set the TopValues property to 10%.

You can't set this property in code directly. It's set in SQL view (SQL view: An object tab that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, the SQL equivalent is constructed in SQL view.) of the Query window (Query window: A window in which you work with queries in Design view, Datasheet view, SQL view, or Print Preview.) by using a TOP n or TOP n PERCENT clause in the SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.).

You can also set the TopValues property by using the query's property sheet or the Top Values box under Tools on the Design tab.

 Note   The TopValues property in the query's property sheet and on the Design tab is a combo box that contains a list of values and percentage values. You can select one of these values or you can type any valid setting in the box.


Typically, you use the TopValues property setting together with sorted fields. The field you want to display top values for should be the leftmost field that has the Sort box selected in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.). An ascending sort returns the bottommost records, and a descending sort returns the topmost records. If you specify that a specific number of records be returned, all records with values that match the value in the last record are also returned.

For example, suppose a set of employees has the following sales totals.

Sales Salesperson
90,000 Leverling
80,000 Peacock
70,000 Davolio
70,000 King
60,000 Suyama
50,000 Buchanan

If you set the TopValues property to 3 with a descending sort on the Sales field, Microsoft Office Access 2007 returns the following four records.

Sales Salesperson
90,000 Leverling
80,000 Peacock
70,000 Davolio
70,000 King

 Note   To return the topmost or bottommost values without displaying duplicate values, set the UniqueValues property in the query's property sheet to Yes.


The following example assigns an SQL string that returns the top 10 most expensive products to the RecordSource property for a form that will display the ten most expensive products.

Dim strGetSQL As String
strGetSQL = "SELECT TOP 10 Products.[ProductName] " _
    & "AS TenMostExpensiveProducts, " _
    & "Products.UnitPrice " _
    & "FROM Products " _
    & "ORDER BY Products.[UnitPrice] DESC;"
Me.RecordSource = strGetSQL

Applies to:
Access 2007