How do I show the sum of each page in a multi-page report?

  • Thread starter Thread starter Reva
  • Start date Start date
R

Reva

I have a report that each row is the sum of [Rate]*[Qty]=[ExtendedPrice]. I
need to show the "carried forward" total of the [ExtendedPrice] at the end of
each place and then the grand total of the [ExtendedPrice] on the final page.
How do I sum each page?
 
Reva said:
I have a report that each row is the sum of [Rate]*[Qty]=[ExtendedPrice]. I
need to show the "carried forward" total of the [ExtendedPrice] at the end of
each place and then the grand total of the [ExtendedPrice] on the final page.
How do I sum each page?
Click to expand...


You don't sum each page. Instead sum all the individual
values. Most of the time you only need a repot footer text
box with an expression like =Sum(Rate*Qty)

You have do it differently for each page's subtotal because
the calculation needs a running sum text box in the detail
section.

If you want the page subtotal to also be a running sum
(instead of a subtotal of just one page), then the page
footer text box would just refer to the detail running sum
text box without any code.
 
Thank you but that is not what I want. By putting that formula in the report
footer I am getting the grand total of the [ExtendedPrice] column at the end
of the report only when I need to show the subtotal of the [ExtendedPrice]
column on each page of the report as well as the grand total on the last page.

Do you have any other suggestions - I'm desperate. The database would be
complete if it weren't for this one problem.

Marshall Barton said:
Reva said:
I have a report that each row is the sum of [Rate]*[Qty]=[ExtendedPrice]. I
need to show the "carried forward" total of the [ExtendedPrice] at the end of
each place and then the grand total of the [ExtendedPrice] on the final page.
How do I sum each page?
Click to expand...


You don't sum each page. Instead sum all the individual
values. Most of the time you only need a repot footer text
box with an expression like =Sum(Rate*Qty)

You have do it differently for each page's subtotal because
the calculation needs a running sum text box in the detail
section.

If you want the page subtotal to also be a running sum
(instead of a subtotal of just one page), then the page
footer text box would just refer to the detail running sum
text box without any code.
Click to expand...
 
Reva said:
Thank you but that is not what I want. By putting that formula in the report
footer I am getting the grand total of the [ExtendedPrice] column at the end
of the report only when I need to show the subtotal of the [ExtendedPrice]
column on each page of the report as well as the grand total on the last page.

Do you have any other suggestions - I'm desperate. The database would be
complete if it weren't for this one problem.

Marshall Barton said:
Reva said:
I have a report that each row is the sum of [Rate]*[Qty]=[ExtendedPrice]. I
need to show the "carried forward" total of the [ExtendedPrice] at the end of
each place and then the grand total of the [ExtendedPrice] on the final page.
How do I sum each page?
Click to expand...

You don't sum each page. Instead sum all the individual
values. Most of the time you only need a repot footer text
box with an expression like =Sum(Rate*Qty)

You have do it differently for each page's subtotal because
the calculation needs a running sum text box in the detail
section.

If you want the page subtotal to also be a running sum
(instead of a subtotal of just one page), then the page
footer text box would just refer to the detail running sum
text box without any code.
Click to expand...
Click to expand...


I do not understand. The grand total in the report footer I
suggested will be on the last page. If that's not what you
need to do, please explain with some details beyond "not
what I want".

I thought you already have the page subtotals working. If
this is the part you are struggling with, please explain
exactly what "carried forward total of the [ExtendedPrice]"
is supposed to be. I can imagine that you want a running
total in each page's Page Footer section OR maybe you want
the Page Footer subtotal to be a subtotal of just the
details on that page.
 
Hi,

The report consists of four major columns/fields [Description], [Rate],
[Qty] and [ExtendedPrice] which using a query is the sum of [Rate]8[Qty]. I
need to show the total of the [ExtendedPrice] column per page. Through trial
and error I found how to carry forward a balance creating a cumulative total
at the end of each page (using the Running Sum in the field properties);
however the report needs to show the total of each page separately i.e. Total
Page 1, Total Page 2, etc. I tried writing a short code but the only help I
could find on that subject did not apply to Access 2007 only 2002/2003.

I do appreciate any help you can offer and I am sorry if I am not explaining
myself clearly - I've just reached the point of frustration to come this far
with the database and not be able to have the main report work as required.

Marshall Barton said:
Reva said:
Thank you but that is not what I want. By putting that formula in the report
footer I am getting the grand total of the [ExtendedPrice] column at the end
of the report only when I need to show the subtotal of the [ExtendedPrice]
column on each page of the report as well as the grand total on the last page.

Do you have any other suggestions - I'm desperate. The database would be
complete if it weren't for this one problem.

Marshall Barton said:
Reva wrote:

I have a report that each row is the sum of [Rate]*[Qty]=[ExtendedPrice]. I
need to show the "carried forward" total of the [ExtendedPrice] at the end of
each place and then the grand total of the [ExtendedPrice] on the final page.
How do I sum each page?

You don't sum each page. Instead sum all the individual
values. Most of the time you only need a repot footer text
box with an expression like =Sum(Rate*Qty)

You have do it differently for each page's subtotal because
the calculation needs a running sum text box in the detail
section.

If you want the page subtotal to also be a running sum
(instead of a subtotal of just one page), then the page
footer text box would just refer to the detail running sum
text box without any code.
Click to expand...
Click to expand...


I do not understand. The grand total in the report footer I
suggested will be on the last page. If that's not what you
need to do, please explain with some details beyond "not
what I want".

I thought you already have the page subtotals working. If
this is the part you are struggling with, please explain
exactly what "carried forward total of the [ExtendedPrice]"
is supposed to be. I can imagine that you want a running
total in each page's Page Footer section OR maybe you want
the Page Footer subtotal to be a subtotal of just the
details on that page.
Click to expand...
 
Reva said:
The report consists of four major columns/fields [Description], [Rate],
[Qty] and [ExtendedPrice] which using a query is the sum of [Rate]8[Qty]. I
need to show the total of the [ExtendedPrice] column per page. Through trial
and error I found how to carry forward a balance creating a cumulative total
at the end of each page (using the Running Sum in the field properties);
however the report needs to show the total of each page separately i.e. Total
Page 1, Total Page 2, etc. I tried writing a short code but the only help I
could find on that subject did not apply to Access 2007 only 2002/2003.
Click to expand...


Almost any code that works in an earlier version will work
in a later version.

I don't know what you found, but there are some ridiculously
complicated ways out there. I think the simplest way is to
use code like the following:

Private Sub PageFooter_Print(Cancel As Integer, PrintCount
As Integer)
Me.txtPageAmt = Me.txtRunAmount - Me.txtPageRunTotal
Me.txtPageRunTotal = Me.txtRunAmount
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount
As Integer)
Me.txtPageRunTotal = 0
End Sub

where:
txtRunAmount - the running sum text box in the
detail section
txtPageRunTotal - an invisible page footer text box just
used in the above calculation
txtPageAmt - the page footer text box that displays the
page total
 
I made a copy of the report and inserted the text boxes using the suggested
names and copied the code exactly but it is still not working. At present
the report is running over 7 pages and consists of 3 "groups". I have the
report set to start each new group on a new page. Group 1 is on one page
only as is group 2. The page total is not showing on page 1. It is showing
on page 2. Group 3 is 5 pages long. The total sum of group 3 is showing in
the PageAmt text box on page 3 only (the sum of the [extendedprice] column
for pages 3 to 7); all subsequent pages show a 0 balance.

Just confirming - the two text boxes in the page header section are both
unbound, correct?



Marshall Barton said:
Reva said:
The report consists of four major columns/fields [Description], [Rate],
[Qty] and [ExtendedPrice] which using a query is the sum of [Rate]8[Qty]. I
need to show the total of the [ExtendedPrice] column per page. Through trial
and error I found how to carry forward a balance creating a cumulative total
at the end of each page (using the Running Sum in the field properties);
however the report needs to show the total of each page separately i.e. Total
Page 1, Total Page 2, etc. I tried writing a short code but the only help I
could find on that subject did not apply to Access 2007 only 2002/2003.
Click to expand...


Almost any code that works in an earlier version will work
in a later version.

I don't know what you found, but there are some ridiculously
complicated ways out there. I think the simplest way is to
use code like the following:

Private Sub PageFooter_Print(Cancel As Integer, PrintCount
As Integer)
Me.txtPageAmt = Me.txtRunAmount - Me.txtPageRunTotal
Me.txtPageRunTotal = Me.txtRunAmount
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount
As Integer)
Me.txtPageRunTotal = 0
End Sub

where:
txtRunAmount - the running sum text box in the
detail section
txtPageRunTotal - an invisible page footer text box just
used in the above calculation
txtPageAmt - the page footer text box that displays the
page total
Click to expand...
 
Reva said:
I made a copy of the report and inserted the text boxes using the suggested
names and copied the code exactly but it is still not working. At present
the report is running over 7 pages and consists of 3 "groups". I have the
report set to start each new group on a new page. Group 1 is on one page
only as is group 2. The page total is not showing on page 1. It is showing
on page 2. Group 3 is 5 pages long. The total sum of group 3 is showing in
the PageAmt text box on page 3 only (the sum of the [extendedprice] column
for pages 3 to 7); all subsequent pages show a 0 balance.

Just confirming - the two text boxes in the page header section are both
unbound, correct?
Click to expand...


Page Header??? You are supposed to use the Page FOOTER, and
yes, both text boxes should have nothing in their control
source.

If I understand what you want, the running sum text box in
the detail section should have an expression like:
=[Rate]*[Qty]
with its RunningSum property set to Over All

Your use of groups is a new piece of information that seems
irrelevant to the problem of getting page subtotals.
 
Page Header was just a typo...i meant to type page footer. The running sum
total is [Qty]*[Rate]. Below is the code I typed (copy & paste)

Private Sub PageFooterSection_Print(Cancel As Integer, PrintCount As Integer)
Me.PageAmt = Me.RunAmount - Me.PageRunTotal
Me.PageRunTotal = Me.RunAmount
End Sub

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)
Me.PageRunTotal = 0
End Sub

PageAmnt Total for Page 1 is blank
PageAmnt Total for Page 2 is correct
PageAmnt Total for Page 3 is the sum of pages 3 to 7

The PageRunTotal and PageAmnt are in the Page Footer section; the RunAmount
is actually in the Section Footer. When I tried putting this text box in the
actual detail of the report the values were way off...seemed almost
triplicated.

Marshall Barton said:
Reva said:
I made a copy of the report and inserted the text boxes using the suggested
names and copied the code exactly but it is still not working. At present
the report is running over 7 pages and consists of 3 "groups". I have the
report set to start each new group on a new page. Group 1 is on one page
only as is group 2. The page total is not showing on page 1. It is showing
on page 2. Group 3 is 5 pages long. The total sum of group 3 is showing in
the PageAmt text box on page 3 only (the sum of the [extendedprice] column
for pages 3 to 7); all subsequent pages show a 0 balance.

Just confirming - the two text boxes in the page header section are both
unbound, correct?
Click to expand...


Page Header??? You are supposed to use the Page FOOTER, and
yes, both text boxes should have nothing in their control
source.

If I understand what you want, the running sum text box in
the detail section should have an expression like:
=[Rate]*[Qty]
with its RunningSum property set to Over All

Your use of groups is a new piece of information that seems
irrelevant to the problem of getting page subtotals.
Click to expand...
 
IT WORKED!!!! I started all over from fresh this morning with a whole new
report and it seems to be finally working.

THANK YOU VERY MUCH FOR YOUR KIND ASSISTANCE, IT WAS GREATLY APPRECIATED :)

Reva

Marshall Barton said:
Reva said:
I made a copy of the report and inserted the text boxes using the suggested
names and copied the code exactly but it is still not working. At present
the report is running over 7 pages and consists of 3 "groups". I have the
report set to start each new group on a new page. Group 1 is on one page
only as is group 2. The page total is not showing on page 1. It is showing
on page 2. Group 3 is 5 pages long. The total sum of group 3 is showing in
the PageAmt text box on page 3 only (the sum of the [extendedprice] column
for pages 3 to 7); all subsequent pages show a 0 balance.

Just confirming - the two text boxes in the page header section are both
unbound, correct?
Click to expand...


Page Header??? You are supposed to use the Page FOOTER, and
yes, both text boxes should have nothing in their control
source.

If I understand what you want, the running sum text box in
the detail section should have an expression like:
=[Rate]*[Qty]
with its RunningSum property set to Over All

Your use of groups is a new piece of information that seems
irrelevant to the problem of getting page subtotals.
Click to expand...
 
Reva said:
IT WORKED!!!! I started all over from fresh this morning with a whole new
report and it seems to be finally working.

THANK YOU VERY MUCH FOR YOUR KIND ASSISTANCE, IT WAS GREATLY APPRECIATED :)
Click to expand...


That's good to hear and a great relief. I was really
struggling to figure out what you had that could possibly
produce the results you were seeing. Starting over is
sometimes a good idea, especially when there have been a lot
of changes that were made to explore different possible
solutions.
 
Back
Top