How to calculate Debit and Credit balance from Opening Balance in Ms Access SQL

Last Reply 7 days ago By dharmendr

Posted 9 days ago

I want this type of output from ms access sql query

Sdate            ob         cr         dr        cb

1-2-2018      2000     0           0          2000

1-3-2018      2000     100       0          2100

1-4-2018       2100    200       0          2300

1-5-2018        2300   0          100       2200

I tried this query but it didn't work for me 

Getting the error like 'at most one record can be retreturn by this sub query ' 

select Edate,OB, Cr, Dr,(select (a.OB + a.Cr - a.Dr) from accdata a where a.Edate<=b.Edate ) as CB 
from accdata b

so help me how to do this in ms access SQL

You are viewing reply posted by: dharmendr 7 days ago.
Posted 7 days ago

Hi ajitn4u,

The balance for each successive row depends on the ending balance from the previous row. You have to write SQL query to loop through each transactions one at a time.