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 payment side of cash book of 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