list of account codes with their respective balances: positive or negative. SELECT Code, Sum( Total) AS Net FROM Query GROUP BY Code; A positive net amount implies a debit and negative amount means a credit balance corresponding to an account code. This is because in Query , the total of credit amount has been made to appear as negative. This query is saved as Query for its subsequent use in generating record set for trial balance.
(e) To find that record set which consists of account code, name of account, debit amount and credit amount : Every row of a trial balance report consists of Account Code, Name of Account, Debit Amount and Credit Amount. The Debit Amount and Credit Amount are mutually exclusive. Such rows are obtained by generating a record set based on the following SQL statement. SELECT a.Code, b.name AS [Name of Account], IIF (a.Net> ,a.Net, null ) AS Debit, IIF (a.Net< ,abs(a.Net) , null ) AS Credit FROM Query AS a, Accounts AS b WHERE a.code = b.code ; In the above SQL statement, the results of Query and data stored in Accounts table has been used.
The SELECT clause of this SQL statement has two computed fields as explained below : IIF(a.Net> ,a.Net,null) AS Debit: According to IIF( ) function, if the net amount exceeds zero, it is displayed as Debit, otherwise nothing appears in Debit field. IIF (a.Net< ,abs(a.Net) ,null) AS Credit: According to IIF( ) function, if the net amount is less than zero (implying negative), it is displayed as Credit, otherwise nothing appears in Credit field. Besides, the other two fields: Code and Name, of SELECT clause are retrieved from Query and Accounts table respectively. This SQL statement is saved as Query for providing the necessary information content for Trial Balance Report.
Model-II : The following series of SQL statements retrieve the record set for producing trial balance when database design for Model-II 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. (a)