Looping question

[raneking22]

I am automating a database for a client and I have run into a problem. I have researched it enough to know that I have to set up a loop. But, I can not find any code that will help me accomplish this. I am doing this in Access 2003.

The problem is:

I have a table with 25k records. Within these records I have around 100 unique manager values. I have built a query to group the managers together. Now what I want to do is to find a way to create 100 individual Excel 97-2003 files based on these manager groupings. So, for instance, 50 records containing value "SteveManager" in the manager field would become an excel spreadsheet named SteveManager.xls, and so on. This data is going to be overwritten every 3 months, so even creating 100 manual queries 1 time won't work because I would just have to redo it the next time since new managers are added/removed. Any help would be greatly appreciated!

Jodie

[MMcCarthy]

I am automating a database for a client and I have run into a problem. I have researched it enough to know that I have to set up a loop. But, I can not find any code that will help me accomplish this. I am doing this in Access 2003.

The problem is:

I have a table with 25k records. Within these records I have around 100 unique manager values. I have built a query to group the managers together. Now what I want to do is to find a way to create 100 individual Excel 97-2003 files based on these manager groupings. So, for instance, 50 records containing value "SteveManager" in the manager field would become an excel spreadsheet named SteveManager.xls, and so on. This data is going to be overwritten every 3 months, so even creating 100 manual queries 1 time won't work because I would just have to redo it the next time since new managers are added/removed. Any help would be greatly appreciated!

Jodie

Can you post a reply to this thread on Wednesday/Thursday to bump it up the list. It may not get much attention over Christmas.

Happy Holidays.

Mary

[Killer42]

Can you post a reply to this thread on Wednesday/Thursday to bump it up the list. It may not get much attention over Christmas.

Sounds as though you'd probably want to use some VBA code to loop through the "manager values" and do a DoCmd TransferText for each.

[raneking22]

What would be a good resource to find example of looping code to run through the managers? I may not have said this in my initial post, but I know very little VBA.

Thanks,

Jodie

[NeoPa]

This isn't that easy for a newbie Jodie, but basically :
1. Produce a query to list each manager including their name in the results (once only per manager).
2. Process through this query in a VBA loop (there are examples of such things here in these forums).
3. For each item create or update an existing QueryDef (saved Access Query) whose SQL restricts the output to those records matching your manager name.
4. Using DoCmd.TransferSpreadsheet, export this newly created or updated QueryDef to create each individual manager's spreadsheet.

You may need help in some of these steps but let us know how far you get on your own first.

[MMcCarthy]

Have a look at the following to get you started.

Basic VBA recordset Query

Happy New Year!

Mary

[NeoPa]

Have a look at the following to get you started.

Basic VBA recordset Query

Happy New Year!

Mary

Brilliant Mary!
A great idea to put some basic code in a link like that.
Now we just wait until KUB's ready with the new section so that people can find them a little more easily.

-Ade.