Changing Criteria in Queries

  • Thread starter Thread starter labportuza
  • Start date Start date
L

labportuza

Hello everyone

I'm new in this forum and I found them very helpful. I need help o
something. How can you run some sort of a looping macro or cod
where the criteria in a query is based on a field in a table, and th
query is run for each record in that table? To illustrate, I have
table for Account Managers with fields for Account Manager Name
Territories, etc

I have a Revenue query wherein the criteria is limited to a specifi
Account Manager at a time. Can I automatically run this query (an
the corresponding report) for every Account manager on the tabl
without changing the criteria manually, as if it's looking at th
table and run them one by one? Any help will be appreciated

Thank you
 
labportuza said:
I'm new in this forum and I found them very helpful. I need help on
something. How can you run some sort of a looping macro or code
where the criteria in a query is based on a field in a table, and the
query is run for each record in that table? To illustrate, I have a
table for Account Managers with fields for Account Manager Name,
Territories, etc.

I have a Revenue query wherein the criteria is limited to a specific
Account Manager at a time. Can I automatically run this query (and
the corresponding report) for every Account manager on the table
without changing the criteria manually, as if it's looking at the
table and run them one by one? Any help will be appreciated.
Click to expand...


It sounds like you only need one query for the report. It
just has to join the two tables:

SELECT ManagersTable.Key
ManagersTable.ManagerName,
ManagersTable.Teritories,
. . .
RevenueTable.SaleDate,
RevenueTable.SaleAmount
FROM ManagersTable LEFT JOIN RevenueTable
ManagersTable.Key = RevenueTable.ManagerKey

Then, in the report, use Sorting and Grouping (View menu) to
create a group with header and footer on the ManagerName
field.

Set the group header section properties as needed to get the
equivalent of a separate report for each manager.
 
Thank you Marsh, I guess my follow-up question is, how do you save o
export a report for each of the manager and then enable the system t
e-mail these reports to the corresponding Managers automatically.
hope I'm not asking too much. thank you. :
 
labportuza said:
Thank you Marsh, I guess my follow-up question is, how do you save or
export a report for each of the manager and then enable the system to
e-mail these reports to the corresponding Managers automatically. I
hope I'm not asking too much. thank you. :D
Click to expand...


You might be asking asking too much. I don't think you can
split a report into pieces.

You are better off keeping a thread to a single question so
that someone else will have a chance to provide a better
answer than I can. Try posting your question to the reports
newsgroup.
 
Back
Top