HELP NEEDED: Running Balance with Data Containing NULL

D

dustonheaven

I need to calculate running balance on data containing Null, which is sorted
by few columns.
As example below, Clr is sorted by Null Desc, then by date, then just by ID.
So far, I just come out with this...

SELECT tr1.Clr, tr1.ID, tr1.Date, tr1.Acc, tr1.Dbt, tr1.Cdt, (SELECT SUM(Nz
(tr2.Dbt) - Nz(tr2.Cdt)) FROM tbl00 AS tr2 WHERE (tr2.Acc = tr1.Acc AND tr2.
Clr <= tr1.Clr)) AS Bal
FROM tbl00 AS tr1
WHERE (((tr1.Acc)=12345) AND ((tr1.Audit)=True))
ORDER BY tr1.Clr, tr1.Date, tr1.ID;

this query can give me the running balance for all data in Clr column not
NULL, but when it come to null, the calculation stopped.

Clr Date ID Acc Dbt Cdt Bal
------------------------------------------------------------------------------
----------------------------------
1 15/10/08 503 12345 100.00 NULL 100.00
2 14/10/08 504 12345 NULL 3.00 97.00
3 16/10/08 499 12345 NULL 150.00 -53.00
4 16/10/08 505 12345 200.00 NULL 147.00
5 18/10/08 506 12345 NULL 2.00 145.00
NULL 18/10/08 500 12345 1.00 NULL 146.00
NULL 19/10/08 499 12345 NULL 5.00 141.00

i was also trying to break the result into 2 sql which 1 as above, calculate
all the non-NULL result and another sql to calculate the remaining that
contain NULL in Clr. In these tries, i trying to add a sequence column to
replace the NULL in Clr column, but also unsuccessful.

Any one can give me a hand???
 
C

Curis

Try adding the optional "valueifnull" argument for the NZ functions you are
using. Right now, the NZ functions are returning zero-length strings for any
null values encountered since they are being used as expressions in a query.

Try: SELECT SUM(Nz(tr2.Dbt, 0) - Nz(tr2.Cdt, 0))

This will explicitly substitute a 0 (zero) for any null values encountered.

I am curious, though... is there a reason why you do not just set the
default value on these number/currency fields set to zero? You would not
have to worry about translating null values.

- Curis
 
D

dustonheaven via AccessMonster.com

Perhaps I am miss something, some more detail in my question.
Actually, Nz is not the problem here. The calculation of BAL can be generate
correctly, for the data where CLR is not NULL. The problem is it stop after
CLR reach NULL (since I sort Null value DESC). After CLR is NULL, I let the
data sorted by DATE, then by ID. For this I try to add argument in (tr2.Acc =
tr1.Acc AND tr2.Clr <= tr1.Clr) to continue the calculation, but of course, I
failed.....
 
Top