Model II:
The following series of SQL statements retrieve the record set for producing trial balance when database design for Model-ll is used. In addition to this, the accounts have been categorised within the trial balance according to the Account Type: Expenses, Revenues, Assets, and Liabilities.
1. To find the Total Amount by which the Accounts have been Debited:
The transacted accounts in design of Model-ll have been stored in AccCode of Vouchers Main and Code of Vouchers Detail:
The following SQL statement is formed to generate the relevant information from Vouchers Details.
voucher Main.Vno= Vouchers Details.Vno
| WHERE |
Type = 0 |
| Group BY |
Code |
Similarly, the following SQL statement is formed to generate the required information from Vouchers Main table.
voucherMain.Vno= VouchersDetails.Vno
WHERE Type = 1
GROUP BY AccCode;
Both the SQL statements are meant to extract similar sets of records, but from different sources. Therefore, the resultant record set of these SQL statements have been horizontally merged using UNION clause as shown below.
The above SQL statement is saved as Query 101 for its subsequent use. The total of debit amount in this query represents the Total with positive amounts.
2. To find the Total Amount by which the accounts have been credited:
In order to ascertain the total amount by which every transacted account has been credited, a query similar to that in (a) need to be formed.
This is achieved by substituting Debitfield in SELECT and GROUP BY clause by Credit field and the sum of amount generated by sum(Amount) is multiplied by -1 so that the final amount assigned to Total field is always negative. Accordingly, the following SQL statement is formed.
In the above SQL statement, the sum of amount has been multiplied by -1 to ensure that the amount of credit is always negative just as amount of debit is taken as positive. This query is saved as Query 102 for its subsequent use.
3. To find a collective record set of Accounts with their Debit and Credit totals:
A collective record set is generated by forming a union query between Query 101 and Query 102 to ensure that the debit and credit amount with respect to each account becomes available for generating the net amount.
Accordingly, the following SQL statement is formed.
| SELECT* |
|
| FROM |
Query 101 |
| UNION |
Select* |
| FROM |
Query101 |
The above SQL statement causes horizontal merger of record sets returned by Query101 and Query102. This SQL statement is saved as Query103 for its subsequent use in next query.
4. To find the Net Amount with which an account has been Debited or Credited:
To generate the net amount, an SQL statement similar to Query04 (designed for query(d) of Model-1) above, is formed as shown below, except that its source of data is Query103 instead of Query03.
| SELECT |
Code, sum(Total) AS Net |
| FROM |
Query 103 |
| Group By |
Code |
This query is save as Query104 for its subsequent use in generating a record set, giving details of information for trial balance.
5. To find the Record set which consists of Account Code, Name of Account Debit Amount and Credit Amount:
This query, which is meant to provide relevant information to the trial balance report, is similar to Query 05 (designed and discussed in (e) of Model-1). Accordingly, the following SQL statement is formed by changing the source of data from Query 05 to Query 105 as shown below.
In above SQL statement, the results of Query104 and data stored in accounts table has been used. This SQL statement is saved as Queryl05 for providing source of information to Trial Balance Report.
Trial Balance with Sorting and Grouping Levels:
In order to prepare a trial balance with all the account duly grouped by and sorted within category of accounts, two additional queries (vi) and (vii) are required.
6. To Find the Record Set of Accounts with their Category and Category ID:
Accounts table is related to Account Type table vide Type field. The following SQL statement, using
INNER JOIN clause, is formed to retrieve the relevant fields of various accounts.
SELECT Accounts.Code, Accounts. Name, Category, Catld FROM Accounts
INNER JOIN AccountType ON Accounts.Type = Account type.Catld;
This SQL statement is saved as Query 106 for its subsequent use in next query.
7. To Find the Record Set consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with Category Details:
This query, when compared with (v) above, reveals that two additional fields Category and Catld are required. Accordingly, the SQL statement stored as Query105 is modified by substituting Accounts table with Query106 to form the following 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, Category, Catld FROM Queryl 04 AS a. Query106 AS b WHERE a.code = b.code;
This SQL statement is saved as Query107 to provide information details for designing trial balance with grouping and sorting of the accounts.
