Query.
You can use the TopValues property to return a specified number of records or percentage of records that meet the criteria you specify. For example, you might want to return the bottom 10 values or the top 25 percent of values in a field.
Note The TopValues property applies only to append, make-table, and select queries.
The TopValues property setting is an Integer 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 set the TopValues property in the query property sheet.
You set this property in SQL view of the Query window by using a TOP n or TOP n PERCENT clause in the SQL statement.
You can also set the TopValues property using the Top Values combo box on the Query Design toolbar.
Note The TopValues property in the query property sheet and on the Query Design toolbar is a combo box that contains a list of values and percentage values. You can select one of these values or you may enter any valid setting into the text box portion of this control.
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 clicked in the query design 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 have the following sales totals.
If you set the TopValues property to 3 with a Descending sort on the Sales field, Microsoft Access returns the following four records.
Note To return the topmost or bottommost values without displaying duplicate values, set the UniqueValues property to Yes.
You can’t set this property in code directly. It’s set when you create a new query using an SQL statement. The TOP n or TOP n PERCENT clause in the SQL statement corresponds to the TopValues property setting.
This example assigns an SQL string that returns the top 10 most expensive products to the RecordSource property for a form.
Dim strGetSQL As String= "SELECT DISTINCTROW TOP 10 Products.[ProductName], "= strGetSQL & "AS TenMostExpensiveProducts, "= strGetSQL & "Products.UnitPrice FROM Products "= strGetSQL & "ORDER BY Products.[UnitPrice] DESC;".RecordSource = strGetSQL