Using Model-III* discuss the series of SQL statements to produce a trial balance up to a particular date.
*Since, only Model-1 and II have been discussed in this chapter, accordingly we have attempted the question using Model-I only.
Using Model-III* discuss the series of SQL statements to produce a trial balance up to a particular date.
*Since, only Model-1 and II have been discussed in this chapter, accordingly we have attempted the question using Model-I only.
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.
Important Note: There is a mistake in the Question No. 10 of the book. The correct question should be on Model- I instead of Model-III.
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.
Important Note: There is a mistake in the Question No. 10 of the book. The correct question should be on Model- I instead of Model-III.
