How to calculate an accumulated balance

[Laxanh]

Hi -

CustNo TransDt TotalChargeAccumBal
A100 2/4/2008 100 100
B201 4/8/2008 300 400
A102 5/3/2008 200 600

I have a table of fields like custno, transdt, totalcharge... as above, how
can i have the field/column 'accumbal' calculated in query/report?

Thanks.

 

[Marshall Barton]

CustNo TransDt TotalChargeAccumBal
A100 2/4/2008 100 100
B201 4/8/2008 300 400
A102 5/3/2008 200 600

I have a table of fields like custno, transdt, totalcharge... as above, how
can i have the field/column 'accumbal' calculated in query/report?

In a query, you can use a subquery to do that.

In a report it is trivially simple by just binding the accum
text box to the charge field and setting its Running Sum
property to Over Group or Over All as appropriate.

 

[Laxanh]

Thanks for quick response. I'm new on this...
How do u write this subquery?
I dont know how to add a value in a row1 (100) to one in row2 (300) but put
that calculation result in row2 (400).

CustNo TransDt TotalChargeAccumBal
A100 2/4/2008 100 100

[quoted text clipped - 3 lines]

I have a table of fields like custno, transdt, totalcharge... as above, how
can i have the field/column 'accumbal' calculated in query/report?

In a query, you can use a subquery to do that.

In a report it is trivially simple by just binding the accum
text box to the charge field and setting its Running Sum
property to Over Group or Over All as appropriate.

 

[Marshall Barton]

It sounds like you might not be ready for subqueries, but it
wouls look something like:

SELECT custno, transdt, totalcharge,
(SELECT Sum(X.totalcharge)
FROM yourtable As X
WHERE X.transdt <= yourtable.transdt) As AccumBal
FROM yourtable

But it would be much simpler and quicker to use a runningsum
text box in the report.
--
Marsh
MVP [MS Access]

Thanks for quick response. I'm new on this...
How do u write this subquery?
I dont know how to add a value in a row1 (100) to one in row2 (300) but put
that calculation result in row2 (400).

CustNo TransDt TotalChargeAccumBal
A100 2/4/2008 100 100

[quoted text clipped - 3 lines]

I have a table of fields like custno, transdt, totalcharge... as above, how
can i have the field/column 'accumbal' calculated in query/report?

In a query, you can use a subquery to do that.

In a report it is trivially simple by just binding the accum
text box to the charge field and setting its Running Sum
property to Over Group or Over All as appropriate.

 

[Laxanh via AccessMonster.com]

*Running Sum in report is way easier
but I've learned something new about subquery on this issue.
Thanks for your help.

It sounds like you might not be ready for subqueries, but it
wouls look something like:

SELECT custno, transdt, totalcharge,
(SELECT Sum(X.totalcharge)
FROM yourtable As X
WHERE X.transdt <= yourtable.transdt) As AccumBal
FROM yourtable

But it would be much simpler and quicker to use a runningsum
text box in the report.

Thanks for quick response. I'm new on this...
How do u write this subquery?

[quoted text clipped - 12 lines]