Question

Briefly explain the set of SQL statements to produce the receipts side of a cash book for Model- I.

Answer 1

The following are the set of SQL statements that are used to produce the receipts side of a Cash Book for Model-I.

Step1: To Ascertain the Total Amount by which the Cash Account is Debited In order to find out the total amount by which Cash Account has been debited in all the accounts the SELECT clause is executed. This clause has two following fields.
(a) A code to identify the account
(b) A code to generate the total by which the account has been debited
SQL Statement
SELECT Credit As Code, Amount, Date
FROM Vouchers
WHERE Debit Like “231*
This SQL statement is saved as query 01.
This statement provides the code and amount of cash received.

Step2: To Generate the Total of Receipts Side
SQL Statement
SELECT Code Sum (Amount) As Total
FROM Query 01
GROUP BY Code
This SQL statement is saved as Query 02

Step3: To Generate the Record Set Consisting of Account Code, Name of Account, Credit Amount and Date
SQL Statement
SELECT a.code b.name As [Name of Account],
IIF (a = Total > 0, a. Total, null as Amount)
FROM Query 02 As a Accounts As b
WHERE a code=b code