Running sum on report

[alex]

Running sum on report

Hello,
Using Access ’03…

I have a report that’s structured like this:

Page Header
User Header
Month Header
Detail
Month Footer
Page Footer

The data looks similar to this:
‘’’’’’’’’’’’’’’’’’’’’’’’’
Jo Smith
January ActionCount ActionRate
Detail A 3 4
Detail B 8 4
Sum 11

Jo Smith
February ActionCount ActionRate
Detail A 1 9
Detail B 6 9
Sum 7
‘’’’’’’’’’’’’’’’’’’’’’’’

What I cannot seem to figure out, is a way to sum (running sum) the
ActionRates. What I want is one action rate per month (it will always
be the same per month).

For example: January is 4; Feburary is (4) + 9 = 13.

To get a running sum in the Month Footer, I’ve been trying this in an
unbound textbox: =sum([ActionRate]) and then setting running sum to
over group. What happens, however, is that the action rates are
counted for every detail; e.g., 4 + 4, and 9 + 9. I only want 4 + 9.

I tried putting the calculations in different headers/footers with no
luck, thinking that I needed to isolate the ActionRate once per month.

I even tried to get a little tricky by summing the action rates and
then dividing by the record count. Problem is, the record count is an
unbound textbox that cannot be used in a calculation (I keep getting
the parameter box when the report runs).

Any help/advice would be appreciated.

Thanks,
alex

 

[Duane Hookom]

You don't generally use Sum() with the running sum.
I would just use a text box in the month footer:
Name: txtActionRateRS
Control Source: ActionRate
Running Sum: Over All
Visible: No

Then add a text box to the Report Footer Section
Control Source: =txtActionRateRS

 

[alex]

You don't generally use Sum() with the running sum.
I would just use a text box in the month footer:
  Name: txtActionRateRS
  Control Source: ActionRate
  Running Sum: Over All
  Visible: No

Then add a text box to the Report Footer Section
  Control Source: =txtActionRateRS
--
Duane Hookom
Microsoft Access MVP

Running sum on report

Hello,
Using Access ’03…

I have a report that’s structured like this:

Page Header
User Header
Month Header
Detail
Month Footer
Page Footer

The data looks similar to this:
‘’’’’’’’’’’’’’’’’’’’’’’’’
Jo Smith
   January                 ActionCount     ActionRate
           Detail A                3               4
           Detail B        8               4
   Sum                     11

Jo Smith
   February                ActionCount     ActionRate
           Detail A                1               9
           Detail B        6               9
   Sum                     7
‘’’’’’’’’’’’’’’’’’’’’’’’

What I cannot seem to figure out, is a way to sum (running sum) the
ActionRates.  What I want is one action rate per month (it will always
be the same per month).

For example:  January is 4; Feburary is (4) + 9 = 13.

To get a running sum in the Month Footer, I’ve been trying this in an
unbound textbox: =sum([ActionRate]) and then setting running sum to
over group.  What happens, however, is that the action rates are
counted for every detail; e.g., 4 + 4, and 9 + 9.  I only want 4 + 9.

I tried putting the calculations in different headers/footers with no
luck, thinking that I needed to isolate the ActionRate once per month.

I even tried to get a little tricky by summing the action rates and
then dividing by the record count.  Problem is, the record count is an
unbound textbox that cannot be used in a calculation (I keep getting
the parameter box when the report runs).

Any help/advice would be appreciated.

Thanks,
alex
.- Hide quoted text -

- Show quoted text -

Duane,

Thanks for responding...
I need the running sum to be calculated over the group. If I put a
textbox in the month footer, set its control source to action rate,
and then running sum to Over Group (or Over All) it does NOT sum. The
value stays the same; i.e., 4 and 9. I'm assuming the visible
property has nothing to do with it...

I need to use each value in a calculation. In the example above, I'm
trying to use 13 in a calculation (an average calculation based on the
number of records per group). In March (as the running sum increases)
I'll use that number for the same calculation.

I hope that doesn't confuse you.
alex

 

[alex]

You don't generally use Sum() with the running sum.
I would just use a text box in the month footer:
  Name: txtActionRateRS
  Control Source: ActionRate
  Running Sum: Over All
  Visible: No

Then add a text box to the Report Footer Section
  Control Source: =txtActionRateRS

Running sum on report
Hello,
Using Access ’03…
I have a report that’s structured like this:
Page Header
User Header
Month Header
Detail
Month Footer
Page Footer
The data looks similar to this:
‘’’’’’’’’’’’’’’’’’’’’’’’’
Jo Smith
   January                 ActionCount     ActionRate
           Detail A                3               4
           Detail B        8               4
   Sum                     11
Jo Smith
   February                ActionCount     ActionRate
           Detail A                1               9
           Detail B        6               9
   Sum                     7
‘’’’’’’’’’’’’’’’’’’’’’’’
What I cannot seem to figure out, is a way to sum (running sum) the
ActionRates.  What I want is one action rate per month (it will always
be the same per month).
For example:  January is 4; Feburary is (4) + 9 = 13.
To get a running sum in the Month Footer, I’ve been trying this in an
unbound textbox: =sum([ActionRate]) and then setting running sum to
over group.  What happens, however, is that the action rates are
counted for every detail; e.g., 4 + 4, and 9 + 9.  I only want 4 + 9.
I tried putting the calculations in different headers/footers with no
luck, thinking that I needed to isolate the ActionRate once per month..
I even tried to get a little tricky by summing the action rates and
then dividing by the record count.  Problem is, the record count isan
unbound textbox that cannot be used in a calculation (I keep getting
the parameter box when the report runs).
Any help/advice would be appreciated.
Thanks,
alex
.- Hide quoted text -

- Show quoted text -

Duane,

Thanks for responding...
I need the running sum to be calculated over the group.  If I put a
textbox in the month footer, set its control source to action rate,
and then running sum to Over Group (or Over All) it does NOT sum.  The
value stays the same; i.e., 4 and 9.  I'm assuming the visible
property has nothing to do with it...

I need to use each value in a calculation.  In the example above, I'm
trying to use 13 in a calculation (an average calculation based on the
number of records per group).  In March (as the running sum increases)
I'll use that number for the same calculation.

I hope that doesn't confuse you.
alex- Hide quoted text -

- Show quoted text -

Duane,

I think I got it...
I took two textboxes and placed them in the month footer (one based on
AcionRate and the other based on that textbox). I then set the
running sum to over group on the second textbox. For some strange
reason, running sum over group will not calculate unless (at least for
me) a calculation is performed in the first textbox; i.e., it's not
enough to just base the control on another control.

Thanks again,
alex

 

[Duane Hookom]

Looking back at your data, I'm not sure why you have to group headers both
with Jo Smith as a value. If the Month field is a group and you use Over All,
then you should see the value increase in each month footer.
--
Duane Hookom
Microsoft Access MVP

You don't generally use Sum() with the running sum.
I would just use a text box in the month footer:
Name: txtActionRateRS
Control Source: ActionRate
Running Sum: Over All
Visible: No

Then add a text box to the Report Footer Section
Control Source: =txtActionRateRS

:
Running sum on report

Hello,
Using Access ’03…

I have a report that’s structured like this:

Page Header
User Header
Month Header
Detail
Month Footer
Page Footer

The data looks similar to this:
‘’’’’’’’’’’’’’’’’’’’’’’’’
Jo Smith
January ActionCount ActionRate
Detail A 3 4
Detail B 8 4
Sum 11

Jo Smith
February ActionCount ActionRate
Detail A 1 9
Detail B 6 9
Sum 7
‘’’’’’’’’’’’’’’’’’’’’’’’

What I cannot seem to figure out, is a way to sum (running sum) the
ActionRates. What I want is one action rate per month (it will always
be the same per month).

For example: January is 4; Feburary is (4) + 9 = 13.

To get a running sum in the Month Footer, I’ve been trying this in an
unbound textbox: =sum([ActionRate]) and then setting running sum to
over group. What happens, however, is that the action rates are
counted for every detail; e.g., 4 + 4, and 9 + 9. I only want 4 + 9.

I tried putting the calculations in different headers/footers with no
luck, thinking that I needed to isolate the ActionRate once per month..

I even tried to get a little tricky by summing the action rates and
then dividing by the record count. Problem is, the record count is an
unbound textbox that cannot be used in a calculation (I keep getting
the parameter box when the report runs).

Any help/advice would be appreciated.

Thanks,
alex
.- Hide quoted text -

- Show quoted text -

Duane,

Thanks for responding...
I need the running sum to be calculated over the group. If I put a
textbox in the month footer, set its control source to action rate,
and then running sum to Over Group (or Over All) it does NOT sum. The
value stays the same; i.e., 4 and 9. I'm assuming the visible
property has nothing to do with it...

I need to use each value in a calculation. In the example above, I'm
trying to use 13 in a calculation (an average calculation based on the
number of records per group). In March (as the running sum increases)
I'll use that number for the same calculation.

I hope that doesn't confuse you.
alex- Hide quoted text -

- Show quoted text -

Duane,

I think I got it...
I took two textboxes and placed them in the month footer (one based on
AcionRate and the other based on that textbox). I then set the
running sum to over group on the second textbox. For some strange
reason, running sum over group will not calculate unless (at least for
me) a calculation is performed in the first textbox; i.e., it's not
enough to just base the control on another control.

Thanks again,
alex
.