CREATE cursor acmast (accode n(6), acname c(50),opamt n(10,2)) SELECT acmast INSERT INTO acmast (accode,acname,opamt) VALUES (1, 'party name 1', 54200) INSERT INTO acmast (accode,acname,opamt) VALUES (2, 'party name 2', 54200) CREATE cursor sales (idno n(6), billno c(6), billdt d, accode n(6), qty n(7,2), amt n(10,2)) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) VALUES (1, 'aa/101', DATE(2011,4,04),1,22,509) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) VALUES (2, 'aa/102', DATE(2011,4,04),2,22,509) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) VALUES (3, 'aa/103', DATE(2011,4,14),1,28,852) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) VALUES (4, 'aa/104', DATE(2011,4,04),2,22,509) INSERT INTO sales (idno, billno , billdt , accode , qty , amt ) VALUES (5, 'aa/105', DATE(2011,4,24),1,18,654) SELECT sales.IdNo, sales.billno,sales.billdt, sales.accode, acmast.acname, acmast.opamt, sales.qty, sales.amt ; FROM sales ; LEFT JOIN acmast ON sales.accode=acmast.accode INTO CURSOR tempcurs READWRITE SELECT T1.BillNo, T1.BillDt, T1.AcCode, AcName, ; IIF(T1.BillDt = T2.BillDt AND DTOS(T1.BillDt) + BINTOC(CAST(T1.IdNo as int)) = T2.Packed, ; CAST(T1.OpAmt as C(12)), SPACE(12)) as OpAmt, ; T1.Qty, T1.Amt ; from TempCurs T1 ; INNER JOIN (select AcCode, MIN(BillDt) as BillDt, ; MIN(DTOS(BillDt) + BINTOC(CAST(IdNo as int))) as Packed ; from TempCurs GROUP BY AcCode) T2 on T1.AcCode = T2.AcCode ; ORDER BY T1.AcCode, T1.BillDt ; into cursor TempCurs ReadWrite BROWSE