been credited, a query similar to that in (a) need be formed, except that the Debit field in SELECT and GROUP BY clause is substituted by Credit field. The sum of amount generated by sum(Amount) is multiplied by - so that the final amount assigned to Total field is always negative. This is because the amount of credit must be a negative amount if amount of debit is taken as positive. 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.
Accordingly, the following SQL statement is formed : S ELECT Credit AS Code, Sum( Amount ) *(- ) AS Total FROM vouchers GROUP BY Credit; This SQL statement is saved as Query to be used as source by next query. (c) To generate a collective record set of accounts with their debit and credit totals : Every transacted account that has been debited (or credited) only appears once in this collective record set. However, those transacted accounts that have been debited as well as credited appear twice in this record set: once with a positive amount and thereafter with a negative amount. This collective record set is generated by executing a UNION query between Query and Query .
SELECT* FROM Query UNION SELECT * FROM Query ; This SQL statement is saved as Query for further processing of its resultant record set. (d) To generate the net amount with which an account has been debited or credited : Once the records of account codes with debit and/or credit totals have been collected, the next logical step is to find out the net amount by which such accounts have been either debited or credited. This is accomplished by forming another aggregate query in which FROM clause uses Query as the data source. The sum of Total for each Code of data source, provided by Query , results in computing net amount for every account.
Accordingly, the following SQL statement is formed to generate a