The following series of SQL statement retrieves a record set for producing trial balance when data base is design for ModeII.
1. To find the total amount by which the account have been debited. The following SQL statement will be formed.
GROUP BY clause retrieves the rows of vouchers table accounts wise because the debit field refers to account code. This SQL statement is saved as Query 01. The total of debit amount in this query is given by total yield with positive amounts.
2. To find the total amount by which the accounts have been credited. The following SQL statement will be formed.
The sum of amount generated by Sum(amount) is multiplied by -1 so that the final amount assigned to total field is always negative. The purpose of using negative values is to differentiate between debit and credit totals for each account and also to facilitate the simple arithmetic summation for obtaining the net amount. This SQL statement is saved as Query 01.
3. To generate a collective record set of accounts with their debit and credit totals. Well this collective record set will be generated by executing a union query between Query01 and Query 02.
The following SQL statement will be followed in this case.
SELECT*
FROM Query01
UNION SELECT*
FROM Query 02
This SQL statement is saved as Query 03.
4. To generate the net amount with which an account has been debited and credited.
The following SQL statement will be formed
SELECT Code, Sum(Total) AS Net
FROM Query 03
GROUP BY Code
A positive net amount implies a debit and negative amount means a credit balance corresponding to an account code. This SQL statement is saved as Query 04 used in generating record set for trial balance.
5. To find that record set which consists of account code, name of account, debit amount and credit amount. The following SQL statement will be formed
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, Accounts ASb
WHERE a.code=b.code;
This SQL statement is saved as Query05 for providing the necessary information content for Trial Balance Report
