Top ten results in a query

It's easy to set a query into the right order but sometimes you only want to see the first few records - the "top ten". The standard Query Designer doesn't let you do this but by switching to the SQL view you can easily get the result you want. The example below gets the ten most expensive items from the Products table of the Northwind database.

Start by creating a query from the products table and adding the ProductName and UnitPrice fields. Set the UnitPrice field into descending order so that the most expensive items are at the top of the list.

[Access Query Designer]

This query will show the items that you want to see but it will also show the rest of the items. The next step is to hide these cheaper items.

Switch to SQL view by right-clicking on the Designer and selecting SQL View from the menu. This will show that the query is based on three lines of SQL:

[Access Query Designer]

Change the first line by adding the words TOP TEN to the first line of the SQL:

[Access Query Designer]

Switch back to the Datasheet View and you'll see the most expensive ten products from the table. You can also add the key word PERCENT and get the query to show the most expensive 10% of the table.

Access Tips

FoxPro Tips

General Tips

 

Related Items

American date formats used by Access SQL

Microsoft Access uses an American date format in SQL commands

Read More

Calculate - an alternative to SQL

The Calculate() function - an alternative to SQL

Read More

FoxPro Gotcha

Common mistakes in Visual FoxPro

Read More

Creating a FoxPro array with SQL

Use _TALLY to avoid problems when you create an array of data with SQL in FoxPro.

Read More

Migrating to Access 2002

Migrating to Access 2002 from earlier versions of the database

Read More