Return query records in groups of 500

[dkimbrell]

I have a query with varying quantities of records in it, say anywhere
from 20-10000 depending on the filter criteria.

I need to export these records in groups of 500. I suppose opening
multiple queries, each with 500 records, is the way to do this.

I know I can use SELECT TOP 500 to return the first group, but how do
I get the subsequent groups, like 501-1000?

 

[John W. Vinson]

I have a query with varying quantities of records in it, say anywhere
from 20-10000 depending on the filter criteria.

I need to export these records in groups of 500. I suppose opening
multiple queries, each with 500 records, is the way to do this.

I know I can use SELECT TOP 500 to return the first group, but how do
I get the subsequent groups, like 501-1000?

A (probably inefficient) subquery:

SELECT TOP 500 <whatever>
FORM <wherever>
WHERE primarykey NOT IN (SELECT TOP 1000 X.primarykey FROM tablename AS X
ORDER BY sortterm)
ORDER BY sortterm;

would get the 1001st throgh 1500th records.

I'll have to think if there's some good way to automate this.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com