Extract table data into separate files...

[Rjc8513]

I have a table of approx. 5000 records. Each sales unit has between 1 and 25 records unique to it. The first field in the table is the ID No. which identifies one unit from another.

I need to extract each unit's data (whether it's 1 record or 25) from the main table and create a separate text file or xls file for each unit.

This operation would be performed each week and each unit's total number of records would differ from week to week.

Any suggestions? Thanks.

Richard...

 

[THoey]

I see two ways to accomplish this and the way you choose all depends on how comfortable you are with VBA coding.

1. Write a function that would loop through the table 25 times (one recordset for each department), writing the data to a file. You might be able to do it in one large loop, but I don't know about any limitations on opening and writing to 25 different data files at one time. This would require you being very comfortable with VBA.

2. Create 25 SQL make table queries to dump each department into a temporary table. Then create a function with 25 TransferText function calls.

There is probably a better idea out there somewhere. I think either way will be a bit tedious, at least until you have it set up.

Hope that helps.. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.