Question

Explain the data base design for Model-I for producing the receipts the series of SQL statements for producing the payment side of cash book for Model-II.

Answer 1

The following series of SQL Statement retrieve a record set of producing the payments side of the Cash Book for Model II.

(1) SELECT Narration, Acc-code As Code Amount

FROM vouchers As v, Details As D

WHERE tType=1 AND V.VNO=D.VNO

AND Acc-code like “141*”.

(2) SELECT Narration, Code, Amount

FROM vouchers As v, Details As D

WHERE tType = 0 AND V.VNO = D.VNO

AND Code Like “141*”.

(3) SELECT Narration, Acc-code As Code Amount

FROM vouchers As v, Details As D

WHERE tType=1 AND V.VNO=D.VNo

AND Acc-code Like “141*”.

UNION

SELECT Narration, Code, Amount

FROM vouchers As v, Details As D

WHERE tType=0 AND V. VNO=D.VNo

AND Acc-code like “141*”.

This SQL statement is source as Query D1

(4) SELECT Code, Sum (Amount) As Total

FROM Query D1

GROUP BY Code

This SQL statement is saved as Query D2.

(5) SELECT a.Code b.Name As [Name of Account],

IIF (a.total>0, a total Null) As Amount

FROM Query D2 As a, Account As b

WHERE a code=b code