handling a large number of records

[Guest]

In my database, I may have up to a million records each month. I'm looking
for some suggestions on how to handle the next step of the process.

The database contains records for about 150 different profit centers. The
end result is I need to have a separate workbook for each profit center and
it will need to contain only the records for that profit center.

I'm currently building a table for each unique profit center ID (because the
number of profit centers can vary each month.

I was thinking of creating a loop to run a select query and send the results
to a separate worksheet in a single workbook. Once all of the sheets have
been created, I was going to create a new workbook for each sheet.

Should I sort the data in the database?

Any suggestions would be greatly appreciated. Thanks again for all of the
help.

 

[Guest]

It would not be advisable to break up the table. That will only complicate
your world. A better approach would be to create a parameter query to use as
the source for your transferspreadsheet. To know what profit centers need to
be included for the transer, create a totals query that has only one field -
the profit center identifier and do a group by on that field. Then when you
are ready to do the transfer:

(Warning - Untested psuedo code)
Open the totals guery
Do While Not rstProfitCenters.eof
qdfExport.parameters(0) = rstProfitCenter![Profit_Center]
set rstExport =qdfExport.OpenRecordset
if rstExport.Recordcount <> 0 Then
rst!Export.MoveLast
rst!Export.MoveFirst
Docmd.TransferSpreadsheet
rstProfitCenters.MoveNext
End Do