Query that returns x number of rows

[John]

This problem seems simple but I can't find the answer.

I'm using Access 97. I want to return a set number of rows
from a table. There is no other criteria other than the
number of rows I want. For example, if I have a table of
9000 records, I may want to run a query that returns 3000
records, then 2000 records, then the remaining 4000
records (each record in the 3 queries being mutually
exclusive).

Put another way - how do you tell Access to give me
records 1-3000, then give me records 3001-5000, then
records 5001-9000?

For you FoxPro programmers I'm looking for something in
Access that equates to the recno() function.

Thanks to anyone who can help.

 

[Michel Walsh]

Hi,



A

SELECT TOP 3000 ... ORDER BY key

would return the first 3000.


SELECT TOP 3000 ... FROM (SELECT TOP 6000 ... ORDER BY key DESC ) .... ORDER BY key

would return the 3000 in the last 6000 (so, the middle 3000 if there are 9000 records, in all)


etc.


Another solution is to rank the records, in a computed expression, from 1 to 9000, and use a where
clause kind of : WHERE rank BETWEEN 3001 AND 6000


Hoping it may help,
Vanderghast, Access MVP