Question

# Using Model-I discuss the series of SQL statements to produce a trial balance up to a particular date.

Solution

## The below mentioned series of SQL Statement retrieve the record set for producing Trial Balance when database design for Model-I is used. 1. To ascertain the total amount by which the accounts have been debited: SQL Statement SELECT Debit As Code, Sum (amount), As total FROM vouchers GROUP BY Debit; This SQL statement is saved as Query 01. The GROUP BY clause retrieves the rows of vouchers table accounts-wise. The total of the debit amount in this query is given by the Total field with positive amounts. 2. To ascertain the total amount by which the accounts have been credited: SQL Statement SELECT Credit As Code, Sum (amount)* (-1) As total FROM vouchers GROUP BY Credit; This SQL statement is saved as Query 02. We can see that the total of the credit amount has been multiplied by (-) 1. This is to ensure that the total credit amount should be in negative on contrary to the positive total of debit. 3. To generate a collective record set of accounts with their debit and credit totals: This collective record set is generated by executing a union query between the above queries viz. Query 01 and Query 02. SQL Statement SELECT* FROM Query 01 UNION SELECT* FROM Query 02 This SQL statement is saved as Query 03. 4. To ascertain the net amount with which an account has been debited or credited: SQL Statement SELECT Code, Sum (total), As Net FROM Query 03 GROUP BY Code This SQL statement is saved as Query 04. A positive net amount implies a debit and negative amount means a credit balance corresponding to an account code. This query can be used for generating record set for Trial Balance. 5. To generate the record set which consists of account Code, name of account, debit amount and credit amount: SQL Statement SELECT a.Code, b.Name As [Name of Account] IIF (a.Net>0,a.Net,null) As Debit, IIF (a.Net<0,abs (a.Net), null) As Credit, FROM Query 04 As a, Account As b WHERE a.code=b.code; This SQL statement is saved as Query 05. This query can be used for providing the necessary information content for the Trial Balance Report

Suggest corrections