amount has been multiplied by - to ensure that the amount of credit is always negative just as amount of debit is taken as positive. This query is saved as Query102 for its subsequent use. (c) 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 Query101 and Query102 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 Query101 UNION Select* FROM Query102; 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. (d) 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-I) above, is formed as shown below, except that its source of data is Query103 instead of Query . SELECT Code, Sum( Total ) AS Net FROM Query103 GROUP BY Code; This query is saved as Query104 for its subsequent use in generating a record set, giving details of information for trial balance.
(e) 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 (designed and discussed in (e) of Model-I). Accordingly, the following SQL statement is formed by changing the source of data from Query to Query105 as shown below : 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 Query104 AS a, Accounts AS b/ WHERE a.code = b.code; In above SQL statement, the results of Query104 and data stored in accounts table has been used. This SQL statement is saved as Query105 for providing source of information to Trial Balance Report. Trial Balance