create table test3 free (date d(8),detail c(10),vou_no n(5),debit n(9,2),credit n(9,2),balance n(10,2),dr_cr c(2)) INSERT INTO TEST3 values({},'Opening',0,0,0,450,'DR') INSERT INTO TEST3 values({^2012-04-01},"",1,796827,0,0,'') INSERT INTO TEST3 values({^2012-04-02},"",2,576385,0,0,'') INSERT INTO TEST3 values({^2012-04-03},"",3,0,500000,0,'') INSERT INTO TEST3 values({^2012-04-04},"",4,0,900000,0,'') INSERT INTO TEST3 values({^2012-04-05},"",5,1000000,0,0,'') INSERT INTO TEST3 values({^2012-04-06},"",6,0,972762,0,'') INSERT INTO TEST3 values({^2012-04-07},"",7,8000,0,0,'') INSERT INTO TEST3 values({^2012-04-07},"",7,0,90000,0,'') SELECT Test3.date,; Test3.detail,; Test3.vou_no,; Test3.debit,; Test3.credit,; SUM(Tst.Balance) AS Balance,; IIF(SUM(Tst.Balance) < 0, "CR", "DR") AS dr_cr; FROM Test3; LEFT JOIN (SELECT Debit-Credit+Balance AS Balance, Date FROM Test3) Tst ON Test3.Date >= Tst.Date; GROUP BY Test3.date,; Test3.detail,; Test3.vou_no,; Test3.debit ,; Test3.credit