Group totals

[wal50]

I calculate several totals in a query. One calculation involves nested iif
statements and another uses this result in another calculation. All the
calculated totals print out accurately in the detail section. The problem
occurs when I try to show totals for each group. The straightforward
calculated fields total properly by group but those related to the nested iff
do not. I get a message that the calculation is too complex.
Any ideas?

Regards,

wal50

 

[AlienzDDS]

Could you try the hidden running sum field?

This would be the field you wish to total in the details section (it can be
hidden) and then using the field properties make it a running sum. Then in
the footer you can pull the last value from the hidden text box into an
unbound text box.

 

[wal50]

Thanks. The running sum gets the calculation to work in the detail section.
In the footer, I can only get the first total of the group (with the control
either running sum property Yes or no). I tried the Last function but that
goes back to the "too complex" message. How do I get the last value of the
running sum in the detail section. That would resolve the issue.

Thanks again.
wal50

 

[wal50]

I got it right. Thanks again for your help to go in the correct direction.
wal50

 

[aaron.kempf]

I have these extensions to group by

select state, count(*) as ct
from employees
group by state with rollup


the 'with rollup' will give you an additional row that has the totals
for this query

 

[aaron.kempf]

and for the record-- Jet only allows you to have ONE query per
report.. SQL Reproting Services sounds like it would greatly simplify
your life!

 

[AlienzDDS]

You're welcome

I got it right. Thanks again for your help to go in the correct direction.
wal50