Running sum on report


A

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
 
Ad

Advertisements

D

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
 
A

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



alex said:
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
 
A

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
alex said:
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
 
Ad

Advertisements

D

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


alex said:
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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Wrong Sum in Report Footer 2
Trying to Count in a Report 3
Sum Record per Record 6
Total in report footer 1
Summing Groups in Reports 2
running sum 3
Sum in report 6
Access Report Incorrectly Summing Grouped Data 1

Top