Splitting large output

[Gates Is Antichrist]

Sorry, this must be FAQ'd everywhere, but I seem to be losing my Google magic.

What is the normal technique for splitting large query output (or table) down to 64K chunks for Excel? Creating a table from the query, then looping through its records in code with a 'long' variable managing it?

I hoped for something less awkward. Can you design a field that is sort of like an "autonumber" - that basically enumerates each record? Then I could query against INT(RecNo/65536) and for criteria 1 make table1, for criteria 2 make table2,etc. This still sucks for having to maintain multiple queries ... unless that part is automatable?

Some clever combinations using "TOP" might be interesting...

Unfortunately, there is not a natural grouping division to design around (e.g., if SaleMonth was a field, giving 12 natural chunks).

I am Access 2000. Standalone VB is not available.

 

[bat17]

you could add an autonuber field to the table that would enumerate it for you.

you could set your query to ask for the criteria first and last record number when it is run.

Peter

 

[mdmilner]

Using Bat17's idea - throw in an autonumber and then (re)create a query as many times as necessary to export it in pieces.

 

[Gates Is Antichrist]

Thanks for the help, bat. I didn't know autonumber was down in the field design choices.

So is this the way it's normally done? I thought I was just inventing a cute use of autonumber. Though autonumber has its idiosyncracies, it was enough for me today; I figured this was just a quick fix.