Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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, make-table, and select queries.

Setting

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't set this property in code directly. It's set 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 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.

Remarks

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

Example

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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×