Microsoft Access Office VB VBA Help and Examples

Top X queries and next top X records

You want to return the top x number of records in query, or want another query to return the next x number of records. It can be done with just two queries using the top criteria and IsNull comparison.

Create a new query and select the table (I'm going to call mine Table1) or query you want to get the top x number of records from. Drag in the field or fields you want in the results and choose the sort order for the fields you want at the top. For example if you want to extract the top 10 surname then firstname, in the surname field choose sort ascending. Then for the firstname field choose sort ascending. Make sure surname appears before firstname in the query otherwise it will sort by firstname then surname.

Once you have done this view the results to make sure sorting is right then go back to the design view. On the toolbar you will see a drop down box with an arrow in it. This is the top n box. Type what you want to return. For instance if you want the top 5 type 5 in the box. If you want the top 10 percent type 10% in the box. View the result and voila!

Now the tricky part the next n records. Save your first query with a meaningful name (Mine is called Query1).  Create a new query and bring in the original Table1 and the new Query1 you created above. For this to work you need to create a relationship between Table1 and Query1 .  Select and drag the fields that uniquely identify each record from Table1 to Query1. This creates a relationship. Select the thin line and double click it to bring up the relationship window. Choose the second option  'Include all records from 'Table1' and only those records from 'Query1' where the joined fields are equal'

Okay drag into the query all the fields that you want returned from table1 again and also the linked field from query1. Click off the view box for the fields from query1 nad in criteria for those fields type IS NULL

This will return all records in table1 that are not in query1. Now just sort by the appropriate fields again and select the top amount from the toolbar again. Run the query and voila!

Here's some sample SQL

Query1 - Top5

SELECT TOP 5 DateList.Days 
FROM DateList 
ORDER BY DateList.Days;

Query2 - Next Top 5

SELECT TOP 5 DateList.Days
FROM DateList LEFT JOIN Query1 ON DateList.Days = Query1.Days
WHERE (((Query1.Days) Is Null))
ORDER BY DateList.Days;
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.