Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
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 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 selectingfrom 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 theand 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.