Calculating a value on a text box from a sub-total

[Jessica240]

Hello everyone! This should be a pretty easy question:



Okay, so I have a report that groups information from a query, and subtotals a payable amount. It looks something like this:

Group A..... Item#... PAYABLE

1...........$50.00
2...........$23.00

subtotal: $73.00



Group B.........Item #.....PAYABLE
3..........$16.00
4..........$200.00

subtotal: $216.00




What I'm trying to then do is have a section on the bottom of the report that takes each subtotal amount (the $73.00 and the $216.00) and multiplies it by 95%. I want it to look like this:

Group A: $69.35
Group B: $205.2


I need it to be in the report footer. So I put in some text boxes, go to their properties, and under the "data" tab where I enter in the control source, I try to type in an expression but none of them work.

I can't get Access to isolate each of the subtotal amounts:


Control Source = [AccessTotalsPAYABLE]*.95

But doing this results in Access taking the grand total from my PAYABLE field instead of each subtotal.... How can I write my expression so that Access will let me use the value from each subtotal?


Thank you in advance for your help!

[pbaldy]

Something like:

=Sum(IIf(Group = "A", Payable, 0)) * .95

[June7]

There is only one textbox for the Subtotal.

If you also want the subtotals to show in the report footer, then need a textbox and an expression for each group:

=Sum(IIf([Group]="A", [AccessTotalsPAYABLE], 0))
=Sum(IIf([Group]="B", [AccessTotalsPAYABLE], 0))

How many groups are there?

Or build a subreport in the report footer.

[Jessica240]

Well the thing is, the groups will change periodically..... The grouping is by department and our company has about 50 different departments. Each report is for one employee who might work in several different departments....

So for example, I have employee Bob who might work in sales one week, customer service and kitchen the next week, then lawn care the third week.

My query calculates the payable amount from each department which I need isolated so I can do my accounting duties with each subtotal from each department.

So it would look like this:

Report header: Employee: Bob Smith

Sales Department

..............Item #....Payable

................5............$300.00
................14...........$415.00

subtotal:..................$715.00

Customer Service Department

..............Item #.......Payable

..................6............. $250.00
..................8..............$350.00

subtotal:.....................$600.00




So I can't use an IIF statement because we have 100+ employees with 50 departments. I would have to create 50 different text boxes for each report so that each text box could have its own iif formula...

That's why I was hoping Access had a way that it could isolate each subtotal into a textbox that could be used for calculating formulas in other text boxes....

[pbaldy]

I would create a totals query against the record source of the report and base a subreport on it.