By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,224 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,224 IT Pros & Developers. It's quick & easy.

Looping question

P: 6
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
Dec 22 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 25 '06 #2

Expert 5K+
P: 8,434
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.
Dec 27 '06 #3

P: 6
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
Dec 27 '06 #4

NeoPa
Expert Mod 15k+
P: 31,660
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.
Dec 28 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Have a look at the following to get you started.

Basic VBA recordset Query

Happy New Year!

Mary
Jan 1 '07 #6

NeoPa
Expert Mod 15k+
P: 31,660
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.
Jan 1 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.