Report's Running Total with Page Movement

  • Thread starter Thread starter Olumide
  • Start date Start date
O

Olumide

I've a problem with my report whenever i use public
variable to compute (e.g the sub total a value field).

my databse is SQl Sever, Access environment as my front
end.

Full explanation of the problem
-------------------------------
I've an amount field on the detail section of the report
that gets its data directly from the table, another
calculated field that gets its value through a public
variabe defined in the report. I've some code written on
the detail and group sections of the report that use the
amount field to compute the sub total.

If i preview the report and i want to move from page 1 to,
let's say, page 10, the report shows wrong sub total. But
if i move one page at a time until i reach the last page,
i will have the correct total. This is because the report
computes the total page by page.

If I don't use public variable to compute sub total, and I
want to move from page 1 to 10 directly, the report shows
correct total (i use =sum(Amount)).

There are times i can't use (=sum(Amount)) especially
whenever i need to retrieve value from another table e.g
=dsum("Amount","CustomerLedgerEntry","CustId='" & CustId
& "'")

it is necessary that i should be able to move from one
page to another and have the correct total with the public
variable.

Please what can i do.

Please pardon me for my composition
 
Olumide said:
I've a problem with my report whenever i use public
variable to compute (e.g the sub total a value field).

my databse is SQl Sever, Access environment as my front
end.

Full explanation of the problem
-------------------------------
I've an amount field on the detail section of the report
that gets its data directly from the table, another
calculated field that gets its value through a public
variabe defined in the report. I've some code written on
the detail and group sections of the report that use the
amount field to compute the sub total.

If i preview the report and i want to move from page 1 to,
let's say, page 10, the report shows wrong sub total. But
if i move one page at a time until i reach the last page,
i will have the correct total. This is because the report
computes the total page by page.

If I don't use public variable to compute sub total, and I
want to move from page 1 to 10 directly, the report shows
correct total (i use =sum(Amount)).

There are times i can't use (=sum(Amount)) especially
whenever i need to retrieve value from another table e.g
=dsum("Amount","CustomerLedgerEntry","CustId='" & CustId
& "'")

it is necessary that i should be able to move from one
page to another and have the correct total with the public
variable.
Click to expand...


The data records in a report are not guaranteed to processed
sequentially. Access may have to backup and reformat a
detail several times or, as you've seen when you jump around
a report preview, it may even skip some events altogether.
What all this means is that you can not use code in event
procedures to calculate a value over multiple records.

As you've noted, the preferred technique is to use the
aggregate functions (Count, Sum, Avg, etc). Another
approach is to use a text box with its RunningSum property
set to Over Group or Over All. Finally, it is not unusual
to have to create a Totals query and either Join it to the
report's record source query or use it in a subreport.
 
Back
Top