Summing expressions per group in a report

[Lex Short]

I have unsuccessfully tried to create a group subtotal in a report. I need the report to add together the amounts in only that group.

I have the following expression in the report:
=IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv Amount])

In the group footer I have a text box that says
source: same formula as listed above
Running sum: Over Group

The result is a running total for the whole report instead of per group.


If I change it to
Running sum: No

Then the result is the amount of the last record on the page.

How can I get the report to show the subtotal for that expression per group?

Many thanks for your help!

EggHeadCafe - Software Developer Portal of Choice
Secure Session State Transfer: ASP to ASP.NET
http://www.eggheadcafe.com/tutorial...e-1ef18cbb92e1/secure-session-state-tran.aspx

 

[Jeff Boyce]

In the Group footer, add a textbox control with a Control Source something
like (untested):

=Sum([TheFieldName])

where [TheFieldName] is your field you wished summed.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Larry Linson]

Running Sum generates a "running sum" with a value for each detail line. It
is not for totals in footers, so set it to "No" for your Text Box

The Sum function creates a sum and is intelligent enough to limit that sum
to detail lines that are in the group of the Group Footer. Try setting the
Control Source of the Text Box in the Group Footer to the expression you
list, inside a Sum function:

= Sum(IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
Amount]))

Generally, if you repeat the expression used for Calculated Controls inside
the Sum, it will work... but it IS possible, sometimes, to have an
expression complex enough to confuse the issue and give.

As an alternative, you may be able to create a Text Box in detail with
Running Sum set to Over Group and just refer to it in the footer, without
the Sum, as

= Reports!NameOfYourReport!NameOfYourCalculated Control

Running Sum of the Text Box in the Footer should be "No".

This should display the value of the Calculated Control from the last detail
line, which, of course, would be the Sum you seek.

If you don't want that running sum to appear in the detail lines, set the
Visible property of the Calculated Control to "No".

Larry Linson
Microsoft Office Access MVP

 

[Alexis Short]

Larry, you are a life saver! Thank you so much for your help. The
first suggestion you gave worked beautifully. Adding =Sum before the
expression in the Control Source works. I was at a loss since I
didn't have a field to reference. Thanks for your quick response!

Running Sum generates a "running sum" with a value for each detail line. It
is not for totals in footers, so set it to "No" for your Text Box

The Sum function creates a sum and is intelligent enough to limit that sum
to detail lines that are in the group of the Group Footer. Try setting the
Control Source of the Text Box in the Group Footer to the expression you
list, inside a Sum function:

 = Sum(IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
Amount]))

Generally, if you repeat the expression used for Calculated Controls inside
the Sum, it will work... but it IS possible, sometimes, to have an
expression complex enough to confuse the issue and give.

As an alternative, you may be able to create a Text Box in detail with
Running Sum set to Over Group and just refer to it in the footer, without
the Sum, as

 = Reports!NameOfYourReport!NameOfYourCalculated Control

Running Sum of the Text Box in the Footer should be "No".

This should display the value of the Calculated Control from the last detail
line, which, of course, would be the Sum you seek.

If you don't want that running sum to appear in the detail lines, set the
Visible property of the Calculated Control to "No".

 Larry Linson
 Microsoft Office Access MVP

I have unsuccessfully tried to create a group subtotal in a report.  Ineed
the report to add together the amounts in only that group.

I have the following expression in the report:
=IIf(Now()>[Begin Date],((Month([End Date])-Month([Begin
Date])+1)-(Month(Now())-Month([Begin Date])))*[Monthly Amount],[Inv
Amount])

In the group footer I have a text box that says
source:  same formula as listed above
Running sum: Over Group

The result is a running total for the whole report instead of per group..

If I change it to
Running sum: No

Then the result is the amount of the last record on the page.

How can I get the report to show the subtotal for that expression per
group?

Many thanks for your help!

EggHeadCafe - Software Developer Portal of Choice
Secure Session State Transfer: ASP to ASP.NET
http://www.eggheadcafe.com/tutorials/aspnet/19f8d04a-6bce-4ee0-8d6e-1...- Hide quoted text -

- Show quoted text -