Report and subreport pulling from same query.

[Patrick Fredrich]

I created a sales report from a query where the user has to enter the month
number he wants to see the data for (just using [Enter month Number] in my
query to filter the month).
When the user runs the report he is asked "Enter Month Number" and the
report displays data as follow:

Month of June
Salesperson 1 forcasted $ Actual$
Customer 1 5$ 2$
Customer 2 4$ etc...
slsp1 totals =sum(forcasted$) =sum(actual$)

Salesperson 2 forcasted $ Actual$
Customer 1 5$ 2$
Customer 2 4$ etc...
slsp2 totals =sum(forcasted$) =sum(actual$)

Month Totals =sum(forcasted$) =sum(actual$)

So far so good all works just fine. Now I am trying to add in the report
footer a recap showing the sum for each salesperson without the customer
detail.
Like this:

Recap for the month:
Slsp1 Total forcasted$ Total Actual$
Slsp2 Total forcasted$ Total Actual$
etc...
I have create a subreport pulling from the same query that only displays the
sum for each salesperson, inserted it in my main report. But when I try to
run the report, the same question: "Enter month number" comes up multiple
time, even after linking my main and subreport using the month number.
Any idea why and how I can work around this ?

 

[Klatuu]

If you are running the report from a form, put a text box on the form for the
user to enter the month. The change your queries to reference the text box
on the form to filter by month rather than have the query ask for it. You
would use something in the query criteria row like:
Forms!NameOfForm!NameOfTextBox

You don't even talk about the year. What happens when you run this a year
from now?

 

[Patrick Fredrich]

No wonder you have MVP in your sig! :0)
This works perfectly. As per your question about the year, it is already
filtered for current year only. For next year I will probably use the same
method to pass the data to the query.

Thank you so much for your very prompt response.

If you are running the report from a form, put a text box on the form for the
user to enter the month. The change your queries to reference the text box
on the form to filter by month rather than have the query ask for it. You
would use something in the query criteria row like:
Forms!NameOfForm!NameOfTextBox

You don't even talk about the year. What happens when you run this a year
from now?
--
Dave Hargis, Microsoft Access MVP

I created a sales report from a query where the user has to enter the month
number he wants to see the data for (just using [Enter month Number] in my
query to filter the month).
When the user runs the report he is asked "Enter Month Number" and the
report displays data as follow:

Month of June
Salesperson 1 forcasted $ Actual$
Customer 1 5$ 2$
Customer 2 4$ etc...
slsp1 totals =sum(forcasted$) =sum(actual$)

Salesperson 2 forcasted $ Actual$
Customer 1 5$ 2$
Customer 2 4$ etc...
slsp2 totals =sum(forcasted$) =sum(actual$)

Month Totals =sum(forcasted$) =sum(actual$)

So far so good all works just fine. Now I am trying to add in the report
footer a recap showing the sum for each salesperson without the customer
detail.
Like this:

Recap for the month:
Slsp1 Total forcasted$ Total Actual$
Slsp2 Total forcasted$ Total Actual$
etc...
I have create a subreport pulling from the same query that only displays the
sum for each salesperson, inserted it in my main report. But when I try to
run the report, the same question: "Enter month number" comes up multiple
time, even after linking my main and subreport using the month number.
Any idea why and how I can work around this ?