Use app×
Join Bloom Tuition
One on One Online Tuition
JEE MAIN 2025 Foundation Course
NEET 2025 Foundation Course
CLASS 12 FOUNDATION COURSE
CLASS 10 FOUNDATION COURSE
CLASS 9 FOUNDATION COURSE
CLASS 8 FOUNDATION COURSE
0 votes
207 views
in Accounting System Using Database Management System by (15.2k points)

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

Please log in or register to answer this question.

1 Answer

0 votes
by (15.7k points)

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

Welcome to Sarthaks eConnect: A unique platform where students can interact with teachers/experts/students to get solutions to their queries. Students (upto class 10+2) preparing for All Government Exams, CBSE Board Exam, ICSE Board Exam, State Board Exam, JEE (Mains+Advance) and NEET can ask questions from any subject and get quick answers by subject teachers/ experts/mentors/students.

...