Using Top Values to export a range of records

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
I have a very large table of 3 million records that I need to export out of ACCDB in chunks of 500k records at a time.

They are sorted in a particular order so their Primary IDs are not sequential.

I can easily export out the first 500k by setting the Top Values property to 500,000. Is there a way to then set the range to 500,001 to 1,000,000?

THanks,

Justin
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Not tested (i rarely use top in Access) but should work like this

select top (500000) * from Table where SomeID < 500000 order by SomeID
select top (500000) * from Table where SomeID >= 500000 and SomeID < 1000000 order by SomeID
select top (500000) * from Table where SomeID >= 1000000 and SomeID < 15000000 order by SomeID

If the IDs are not really sequential then you can't be sure of getting exactly 500,000 per chunk.
If you need to get exactly 500,000 per chunk then you have to store the ID's of what is exported so you can start the next batch from the same place.

Probably not ideal. Best to test your algorithm on a small table with say 10 chunks of 10, with only 100 records (more or less).

Another possibility - export the relevant IDs into a temporary-ish table, partition them into the right number of groups, and use that table to control the exports. This would provide a better audit and probably a better way of ensuring reliability (which can also be helped by recording what is exporting and validating the counts).
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top