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; |
| 