Grand Total Percentages Using Complex Calculations

[SharonInGeorgia]

Sample Calculations:
NetProfit = (Income - Expense) / TotIncome
NetProfit % = TotIncome / NetProfit
Income = IncItem1 + IncItem2 + IncItem3
Expense = ExpItem1 + ExpItem2 - ExpItem3

Some of the fields in the report are straight from the table. Many of the
report fields, however, are calculations created as expressions in a query.
My problem is getting the percentage calculations into the Section and Grand
Total of the report. I tried Sum([NetProfit])/SumOf[TotIncome] but for some
reason, I am not getting a sub-total number. I tried created a second query
from the first query using Group by and recreating the expressions
calculatins then creating a subform with the Sub and Grand Total. IT works,
however, there's got to be a better way. Suggestions?

 

[strive4peace]

Hi Sharon,

you have to repeat the equations

change
Sum([NetProfit])/SumOf[TotIncome]
(this has invalid syntax anyway)

to

=Sum((Income - Expense) / TotIncome)
/ [controlname if bound]|equation if calculated

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

 

[SharonInGeorgia]

Crystal,
Repeating the calculations does not work because Access inserts a "FIRST" in
the field within the group section of the report. I get an error msg such as
"Cannot use" =(FIRST)(SUM)(Income - Expense) / TotIncome).

Hi Sharon,

you have to repeat the equations

change
Sum([NetProfit])/SumOf[TotIncome]
(this has invalid syntax anyway)

to

=Sum((Income - Expense) / TotIncome)
/ [controlname if bound]|equation if calculated

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com

Sample Calculations:
NetProfit = (Income - Expense) / TotIncome
NetProfit % = TotIncome / NetProfit
Income = IncItem1 + IncItem2 + IncItem3
Expense = ExpItem1 + ExpItem2 - ExpItem3

Some of the fields in the report are straight from the table. Many of the
report fields, however, are calculations created as expressions in a query.
My problem is getting the percentage calculations into the Section and Grand
Total of the report. I tried Sum([NetProfit])/SumOf[TotIncome] but for some
reason, I am not getting a sub-total number. I tried created a second query
from the first query using Group by and recreating the expressions
calculatins then creating a subform with the Sub and Grand Total. IT works,
however, there's got to be a better way. Suggestions?