Select Range of Records

[Michael]

Hi Folks - I have a 500,000 record table. I need to select and export the
first 100,000 records, then records 101,000 thru 200,000, then 201,000 thru
300,000, etc. Is there a way to select the first 100,000 records, then the
next 100,000 records, etc.? Thanks.

Michael

 

[Guest]

One way is to use the TOP function and flag field to indicate those
previously selected.

Add a field for Exported. Create two queries - first select the TOP
100,000, the second flags those using an update query (can be a Yes/No or a
text field for an "X").

The first has criteria for Exported field NOT flagged.

The second query flags the first 100,000 as being pulled and has criteria
for Exported field NOT flagged.

Then the first query is used again with criteria not to pull those flagged
as having been pulled already.

 

[John Spencer]

Something like the following should work, but the query will be slow due to
the use of the NOT IN.

'First 100000 is easy (and quick)
SELECT Top 100000 *
FROM YourTable
ORDER BY YourPrimaryKey

'Next increment is going to be slower
SELECT Top 100000 *
FROM YourTable
WHERE YourPrimaryKey Not IN
(SELECT Top 100000 *
FROM YourTable
ORDER BY YourPrimaryKey)