Top ten results in a query

It's easy to click in the 'Sort' box of the Access QueryDesigner an set a query into the right order so that you see the most expensive products first but sometimes you only want to see the first few records - the "top ten" most expensive items for example.

The standard Query Designer in Microsoft Access doesn't let you do this but by switching to the designer's SQL view you can easily get the result you want. The example described below gets the ten most expensive items from the Products table of the Northwind database.

Using SQL View in the Access Query Designer

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.

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:

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

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.