Sum of Detail Fields and Group Total

[Guest]

Wacky question: How do I add together amounts for all the records in the
detail section of a report, AND the group totals too? Especially when some of
the numbers in the detail section are negative numbers, but I need them
treated as positive numbers when I add them all up. Please note: I am adding
amounts for each record AND the group total too, to arrive at one number.

Why am I doing it this way? Long story. Trust me - it has to be done this way.

Many thanks,
GwenH

 

[John Spencer]

= Sum(Abs(WhateverField))

The above should add all the numbers together and change negative numbers to
positive numbers in the process.

Since I would assume that the Group totals are composed of the sum of the
details for that group I would use

= 2 * Sum(Abs(WhateverField))

Or if the Group Totals added the negatives as negatives

=Sum(Abs(WhateverField)) + Sum(WhateverField)

 

[Guest]

Thanks! Your third (and last) suggestion was what I needed. Now I need to
know how to total these fields in the report footer. Any ideas?

 

[John Spencer]

Probably all you need to do is copy the control that is doing this at the
group level and paste it into the reports footer. NOT into the page footer
as that will not work.

 

[Guest]

Sorry, that didn't work. It seems that Access 2003 takes any calculated field
in the report total and tries to total what it perceives as the group totals.
On this particular report, however, the group totals are summing positive and
negative numbers, so adding the group totals will not give you a true report
total.

 

[John Spencer]

In a control in the report's footer the following formula should give you
the total for the report.
=Sum(Abs(WhateverField)) + Sum(WhateverField)

As far as I know it doesn't use the values in the group footers. It uses
the values of the field. It works as if you had executed a query to get the
value against the record source for the report.

I think you are saying the result returned was incorrect, but I can't
decipher how it was incorrect.

You can try different formulas.
Total of all values
= Sum(WhateverField)

Total all positive values
= Sum(WhateverField>0,WhateverField,Null)

Total all negative values
= Sum(WhateverField<0,WhateverField, Null)

Total all negative values, but return as a positive number
= Abs(Sum(WhateverField<0,WhateverField, Null))

 

[rikim patel]

Wacky question: How do I add together amounts for all the records in the
detail section of a report, AND the group totals too? Especially when some of
the numbers in the detail section are negative numbers, but I need them
treated as positive numbers when I add them all up. Please note: I am adding
amounts for each record AND the group total too, to arrive at one number.

Why am I doing it this way? Long story. Trust me - it has to be done this way.

Many thanks,
GwenH