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 select the most expensive products first but sometimes you only
want to see the first few records - the "top ten" most expensive items for
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
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
from the menu. This will show that the query is based on three
lines of SQL:
Change the first line by adding the words
to the first line of the SQL:
Switch back to the
and you'll see the most expensive ten products from the table.
In general, SELECT TOP N will seelct the first n
records from the table. You can also add the key word
PERCENT and get the query to show the most
expensive 10% of the table.