with Sorting and Grouping levels : In order to prepare a trial balance with all the account duly grouped by and sorted within category of accounts, two additional queries (f) and (g) are required. (f) To find the record set of accounts with their category and category ID : Accounts table is related to AccountType table vide Type field. The following SQL statement, using INNER JOIN clause, is formed to retrieve the relevant fields for various accounts. SELECT Accounts.Code, Accounts.Name, Category, CatId FROM Accounts INNER JOIN AccountType ON Accounts.Type = Account type.CatId; This SQL statement is saved as Query for its subsequent use in next query.
(g) To find the record set consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with category details : This query, when compared with (e) above, reveals that two additional fields: Category and CatId are required. Accordingly, the SQL statement stored as Query105 is modified by substituting Accounts table with Query106 to form the following 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, Category, CatId FROM Query104 AS a, Query106 AS b WHERE a.code = b.code ; This SQL statement is saved as Query107 to provide information details for designing trial balance with grouping and sorting of the accounts. .
. Procedure in Access for Designing a Simple Trial Balance The Trial Balance is generated using the Design View method by following the steps listed below : (i) Select Reports from objects list provided by LHS of Database Window and click at New object button of tool bar. Access responds by displaying the New Report Window as shown in figure . Choose Design View from list of methods and Query from combo control meant to provide data source to the report.
Click OK after choosing method and data source of report. (ii) Access responds by displaying a blank report design divided horizontally into three sections: Page Header, Detail and Page Footers. Besides, a list of available fields of Query is also provided for embedding on