Running Total from a calculated value

Samuel · External Since: Oct 08, 2009 Posts: 1

All,
Is there a way to do a running total on a control whose control source
is a calculated field?
There is a groupheader0 in this form.

Example
Sam
Payment: 10.00
Payment: 12.00

Total : 22

Juan
Payment: 15.00

Total: 15.00

The control which displays the payment value is a complex calculated
field based off other items on the report.
It is not possible to do the calculations within the limited
capabilites of MS-Access Query. If it was a SQL Server (no chance of
that happening), I could do the calculations within query using T-SQL
if then statements.

Thanks,
Sam

Klatuu · External Since: Apr 06, 2005 Posts: 1360

Certainly Jet is not as powerful as SQL Server, but are you aware you can
execute VBA functions from a Jet query? I have found it a way enhance the
calculation power.
--
Dave Hargis, Microsoft Access MVP

"Samuel" wrote:

> All,
> Is there a way to do a running total on a control whose control source
> is a calculated field?
> There is a groupheader0 in this form.
>
> Example
> Sam
> Payment: 10.00
> Payment: 12.00
>
> Total : 22
>
> Juan
> Payment: 15.00
>
> Total: 15.00
>
> The control which displays the payment value is a complex calculated
> field based off other items on the report.
> It is not possible to do the calculations within the limited
> capabilites of MS-Access Query. If it was a SQL Server (no chance of
> that happening), I could do the calculations within query using T-SQL
> if then statements.
>
> Thanks,
> Sam
>

Marshall Barton · External Since: Dec 07, 2003 Posts: 5120

Samuel wrote:
>Is there a way to do a running total on a control whose control source
>is a calculated field?
>There is a groupheader0 in this form.
>
>Example
>Sam
> Payment: 10.00
> Payment: 12.00
>
>Total : 22
>
>Juan
> Payment: 15.00
>
>Total: 15.00
>
>The control which displays the payment value is a complex calculated
>field based off other items on the report.

Report text boxes have a RunningSum property that will do
that.

Add another text box (named txtRunTotal) to the group footer
section and set its control source to this kind of
expression:
=[the total text box name]
and set its RunningSum property to Over All

Then a report footer text box can display the grand total by
using the expression:
=txtRunTotal

--
Marsh
MVP [MS Access]