query criteria read from table

[drewetzel]

Can someone tell me if there's a way to apply a list of items as criteria in the order listed? Say I've got a list of sales reps and I want a macro to run a query repeatedly, populating one rep from the list at a time in a field called Sales Rep.

Surely there's a way to do something along those lines. Any ideas?

Thanks,

[orange]

What are you trying to accomplish? There may be options.

[drewetzel]

Basically I'm generating a dynaset that shows statistics on a call centers advertising campaigns for a date range (i.e. calls, sales, etc...). I want to generate a seperate report with all the same data but limited to only one sales rep. So calls taken and sales made by rep a for x, y and z campaigns. The fun part comes in doing this for every rep in the call center. I'd much rather have a macro run the query repeatedly and set the rep name criteria for each different rep name, than manually run the query over and over myself.

[orange]

What are the tables involved? How are tables related?
In very general terms it seems you have lots of Sales, Calls ... data for several people over a time period, and you may be able to get what you want by means of a query or two, and grouping the various numbers by Rep. But then again, you know the database and table structure, and what you hope to get out --- much better than any of us.

The concept is like this
http://www.w3schools.com/sql/sql_groupby.asp

[drewetzel]

That's exactly right and I know of the method you're referring to. I actually already have it set up that way. Group on Sales Rep, Group on Advertising Campaign and count/sum calls and sales.

It's just not as clean as I'd like it to be. It spits out a massive dynaset with the rep name repeating for every campaign. This will definitely get the job done. I was just wondering if there was a way to have a seperate dynaset for each agent (without having to manually set the criteria each time it runs).

[orange]

Can you post a picture of the problem?
If you only want a report for 1 rep, then set up a form that allows you to select the rep.
Use that selection in the query that is the source of the data for the report.

Well if there's a rep and multiple campaigns, don't you have to show which rep your stats apply to and the campaign....

[drewetzel]

I do want a report for all reps but a seperate report for each rep, so if I used a form I'd have to manually set the criteria for each rep.

I think I found a solution though. This may seem convoluted, but...

I'm going to create a temp version of my employees table (sales reps) and add a "completed" column. I'll query that table with "is null" as the criteria for the "completed" column and set the "return" dropdown to 1, so it will only return the first employee with a null value for the completed column. I'll link this to the queries I'm using to generate my Ad Campaign report. That way it will only run for one sales rep. Then I'll make a copy of the query with the null criteria for the completed column, but have it update that column with the text "completed" or something. Then when I run the Ad Campaign queries again the first result with a null value for the completed column will be the next sales rep in the table.

I'll have a macro run all of these queries so I can just sit back and watch (or work on other things if I'm feeling productive )

[orange]

Good luck.
You're creating paper reports?

[drewetzel]

Thanks. Well, they're going to be excel exports, that may or may not be printed.