Calcualte a total based on other groups in the same report footer

[Ianw]

Hi,

I have created a report and used the grouping functionality to group 3 sets of data. However, the first two groups are summed in the group footer. However, I want to be able to divide group 2 into group 1 to come up with a rate in the 3rd group.

Example

GROUP 1



Accum Depn A - $10,000
B - $20,000
SubTotal_Accum $30,000 This Subtotal is a sum of A & B

GROUP 2
Depreciation Expense C - $1,000
D - $4,000
SubTotal_ DepnExp $5,000 This Subtotal is a sum of C & D

GROUP 3
Rolling Current Year Trend
10.00
5.00
Sub Total 6.00 This SubTotal is SubTotal_Accum/SubTotal_DepnExp


I am using Access 2010

Thanks

Ian

[June7]

Will probably need Sum(IIf(...)) expressions in report footer to calculate sum of A&B and sum of C&D and then those textboxes can be referenced.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Ianw]

Thanks June7. I tried what you suggested.

I inserted 2 text boxes in the group footer and entered the formula in the control source as follows. SORT_ORDER being the ID of each group where SORT_ORDER number 1 is the Accum Depreciation and 2 is the Depreciation Expense.

=Sum(IIf([SORT_ORDER] In (1),([CURRENT_MONTH_12]),0)) I renamed the control AccumDep
=Sum(IIf([SORT_ORDER] In (2),([CURRENT_MONTH_12]),0)) I Renamed this control DepExp

These formulas give me the answer I am Looking for in each group sub total.

I then created a inserted a third text box and referenced the previous two text boxes as follows. I don't seem to get an answer.

=Sum(IIf([SortOrder]=3,([AccumDep]/[DepExp]),0))

I hope that makes sense.

Thanks

Ian

[June7]

Aggregate functions cannot reference textboxes. They must reference fields.

[Ianw]

I may be using the wrong terminology. I used the iif statement in the aggregate field in the group footer and then created another aggregate field and then divided one into the other. So they do reference a field.

[June7]

AccumDep and DepExp are names of textboxes. You are trying to reference these textboxes in Sum() function. Won't work.

AccumDep and DepExp already have aggregate sums and already filtered by IIf, why do this again?

Then:
=[AccumDep]/[DepExp]

[Ianw]

I guess I am trying to get the take 2 aggregate groups and then divide one by the other to get a ratio for the third group. Sounds like it can't be done. Thanks anyway. I appreciate your time.

[June7]

I am sure there is a way but need better understanding of data.

Options could involve report/subreport or nested queries or domain aggregate functions or just getting the Sum(IIf()) calcs into correct section on report.

[Ianw]

I'll have another look on Monday and try something different. Thanks.

[Ianw]

June7. I went the route of subreports in the report footer and was able to get what I needed. Thanks for the suggestion.