Report Total from a query

[Lorlai]

I have searched online and haven't been able to find a solution for this problem. I have a report that groups on Resource. Each resource is displayed as a single line on the report, and contains fields that apply to the resource, such as budgeted hours, actual hours, and total wages. I am trying to display a running total in the report footer for the budgeted hours, actual hours, and total wages fields. The main report (grouped by resource) will only show resources that have actual hours recorded. Currently, my report footer totals are displaying the budgeted hours for EVERY resource, and for every month, not the resources (and filtered month) displayed by my report query. For the month of April, my budgeted total should be 2,000, not 55,000 hours.

Is there a way to have the running total within my report footer equal only resources that are filtered within my report?

Thank you for your help!

[pbaldy]

You haven't shown how you get the totals, but it would seem like you could base them on the query instead of the table.

[Lorlai]

Thank you for your response PBaldy, I apologize for not being more clear. I have tried basing the totals on table and on the query, and receive the same results. Please see below for a sample of the database design:

Budget

Resource Yr MoNum (as number, such as January = 1) MonthHrs

My query (titled budgetmonthcalc) to get the budget from the user entered period is as follows:

SELECT Budget.Resource, Budget.MonthHrs AS BudgetMonthHrs
FROM Budget
WHERE (((Budget.MoNum)=Month(CVDate((Forms!Form1!PeriodTextBox.Value)))) And ((Budget.Yr)=Year(CVDate((Forms!Form1!PeriodTextBox.Value)))));

Where the parameters are pulling the date based upon a user entered period on form1.

When setting the textbox in the report footer section to =Sum([budgetmonthcalc].[BudgetMonthHrs])

I still get the 55,000 budgeted hours for all resources/months, despite my query displaying the correct resources and month budget hours.

Does this explain in more detail my situation? Is there something that I could change to fix this?

[pbaldy]

You said the main report only shows filtered records. How is it filtered?

[rzw0wr]

Try removing the Sum().
Create a hidden text box in the section where your budgetMonthHrs is, type = BudgetMonthHrs, and setting the running sum to Over group.
In the footer text box type =ThetextBoxYouMade. Leave the Running Sum at no.

Dale

[Lorlai]

Thank you Dale, this worked perfectly!! You have saved me many hours of beating my head against the wall