Opening Balance and Closing Balance

[SHIVA2016]

Hii


Having problem with opening & Closing balance query the closing balance are not getting reflected as opening balance month wise. please help me

screen shot of Table and query are as below



Sql:

SELECT DISTINCT t1.mth, (SELECT (sum(credit - debit)) FROM test AS t2
WHERE t2.mth <= t1.mth) AS op_bal,
Sum(t1.credit) AS Cr, Sum(t1.debit) AS Dr,
Sum((Nz([op_bal])+Nz([credit]))-Nz([debit])) AS cl_bal
FROM test AS t1
GROUP BY t1.mth
ORDER BY t1.mth;

Thank you

[CJ_London]

your opening balance should not include the current months values

WHERE t2.mth <= t1.mth) AS op_bal

should be

WHERE t2.mth < t1.mth) AS op_bal

and your closing balance does not look right either -looks like dec-2004 should be -892, not -4000. Not tested but suspect should be

(SELECT (sum(credit - debit)) FROM test AS t2) WHERE t2.mth <= t1.mth) + sum(nz(credit)-nz(debit)) AS Cl_Bal

not sure why you are using distinct, your group by is already doing that.

also think you need to use the nz function in your subquery

[SHIVA2016]

Thank you, My problem has been solved after dropping the Keyword DISTINCT and modifying the WHERE clause
Its been a great help