Account 6 Marks Question

The procedure of creating the receipts side of cash book is similar to report generation.
To produce the receipts side of the Cash Book, it is essential to retrieve a set of processed data records providing information on Code (Account Name), Name Accounts, Particulars, Date Debit Balance and Credit Balance with reference to each particular account, where cash is debited.
In order to get all this information, it is necessary to make certain SQL statements and save them as Query in the Database.
In Access, the receipts side of a Cash Book can be created by following the below mentioned steps.In Access, the receipts side of a Cash Book can be created by following the below mentioned steps.

1. Select the ‘Create Report’ in the design view. With this, the access responds by displaying a blank report design divided into three sections- Page Header, Detail and Page Footer of the available fields of the Query.
2. Now, we design our report with the tools provided by the Access such as, naming the report, defining its size, colour, etc.
3. Select all the fields from the Query, drag and drop them into the ‘Details Sections’. Hence, the receipts side of the Cash Book is created.

The following are the set of SQL statements that are used to produce the receipts side of a Cash Book for Model-I.
Step1: To Ascertain the Total Amount by which the Cash Account is Debited
In order to find out the total amount by which Cash Account has been debited in all the accounts the SELECT clause is executed. This clause has two following fields.

1. A code to identify the account
2. A code to generate the total by which the account has been debited

SQL Statement

SELECT Credit As Code, Amount, Date

FROM Vouchers

WHERE Debit Like “231*

This SQL statement is saved as query 01.

This statement provides the code and amount of cash received.
Step2: To Generate the Total of Receipts Side

SQL Statement

SELECT Code Sum (Amount) As Total

FROM Query 01

GROUP BY Code

This SQL statement is saved as Query 02
Step3: To Generate the Record Set Consisting of Account Code, Name of

Account, Credit Amount and Date

SQL Statement

SELECT a.code b.name As [Name of Account], IIF (a = Total > 0, a. Total, null as Amount)

FROM Query 02 As a Accounts As b

WHERE a code = b code

The following are the steps involved in creating a query using the wizard method.

1. In the Database window, select ‘Queries’ from Object list given on the Left Hand Side (LHS) of the Database window.
2. Double click at ‘Create Query’ by using wizard given on the Right Hand Side (RHS) of the window. With this ‘Simple Query Wizard’ will be shown on the screen. Now, the user is required to select a field name from a table or from an existing query which should be included in the query being created.
3. Click ‘Next’ after the desired fields have been selected. In case, a number or currency field is included in the selected field, the user is asked to choose an option button to specify whether the query to be created is a summary or detail query.
4. Click ‘Next’ after the desired fields have been selected. In case, a number or currency field is included in the selected field, the user is asked to choose an option button to specify whether the query to be created is a summary or detail query.

1. Programmed Reports: These reports contain information useful for decisionmaking situations that the users have anticipated to occur. There are two types of reports within this report type: Scheduled and On demand.

• Scheduled Reports: The reports, which are produced according to a given time frame, are called scheduled reports. The time frame may be daily, weekly, monthly, quarterly or yearly. Some examples of scheduled reports are: Trial Balance, Ledger, Statement of Cash Transactions (Cash Book), Statement of Ageing Accounts, Closing Stock Report, Profit and Loss Account and Balance Sheet, etc.
• On Demand Reports: The reports, which are generated only on the triggering of some event, are called On demand reports. Some examples of On demand reports are a Customer’s Statement of Account, Inventory Re-order Report, Stock in hand Report for a Selected Group of items, etc.

2. Casual Reports: There are reports, the need for which is not anticipated, the information content of which may be useful but casually required. These are adhoc reports and are generated casually by executing some simple queries without requiring much of professional assistance. As opposed to programmed reports, casual reports are generated as and when required.

The following series of SQL Statement retrieve a record set of producing the payments side of the Cash Book for Model II.

1. SELECT Narration, Acc-code As Code Amount

FROM vouchers As v, Details As D

WHERE tType = 1 AND V.VNO = D.VNO

AND Acc-code like “141*”.

2. SELECT Narration, Code, Amount

FROM vouchers As v, Details As D

WHERE tType = 0 AND V.VNO = D.VNO

AND Code Like “141*”.

3. SELECT Narration, Acc-code As Code Amount

FROM vouchers As v, Details As D

WHERE tType = 1 AND V.VNO = D.VNo

AND Acc-code Like “141*”.

UNION

SELECT Narration, Code, Amount

FROM vouchers As v, Details As D

WHERE tType = 0 AND V. VNO = D.VNo

AND Acc-code like “141*”.

This SQL statement is source as Query D1

4. SELECT Code, Sum (Amount) As Total

FROM Query D1

GROUP BY Code

This SQL statement is saved as Query D2.

5. SELECT a.Code b.Name As [Name of Account],

IIF (a.total > 0, a total Null) As Amount

FROM Query D2 As a, Account As b

WHERE a code = b code

In the access environment a function is named and followed by parenthesis ( ). The function takes some inputs as its argument and in turn sends a value. These functions act as a part of the expression for a computer field. In the SQL environment, following are the main types of functions that are used along with their respective purposes.

1. Domain Aggregate Functions: These functions are used to perform calculation that are based on the values in a field of a table or query. A domain aggregate function uses the syntax as- DFunction (“Fld Name”, “Tbl Name” or Qry Name, “Srch Cond”)

where,

DFunction represents Domain Aggregate Function

Fld Name represents Name of the field to be searched

Tbl Name or Qry Name represents Name of the table or query that contains the above field.

Srch Cond represents Search Condition with the help of which relevant record is searched.

2. SQL Aggregate Functions: These functions are used in those SQL statements that provide the underlying record source of forms and reports. All these functions when used in SQL statement require GROUP BY clause.

3. Other Functions: Apart from Domain Aggregate Function and SQL Aggregate Function, there are some other functions also that are used in SQL. These are mentioned below.

1. IIF: These functions are used to provide a value to the field from a mutually exclusive set of values.

2. Abs: These functions are used to provide the absolute value in return of a numeric value. It takes the absolute value as its input argument and gives back an absolute value.

3. Val: These functions are used to provide numbers contained in a string as a numeric value of appropriate type.

A report design can be refined using the following ways.

1. Adding Dates and Page Numbers: In the design mode, the page footer of a report contains two unbound controls- ‘current date’ and ‘current page number of the total number of pages’. The current date can be retrieved from the RTC using ‘Now ( )’ function. Similarly, the date and time can also be added by clicking ‘Insert % date and time’ from the menu bar to open the Date and Time dialog box.

2. Adding and Deleting Report Controls: After designing the report, one can add or delete additional report controls by the same procedures as of forms.
3. Conditionally Formatting the Report Controls: With the help of Conditional Formatting, the designer can apply special text formats that depend on the value of a field. Such a formatting helps to draw the attention of the users or readers of the report to some values of particular interest.
4. Grouping Levels and Sorting Order: The Grouping Levels on one hand organises the information content of a report into various categories, while on the other hand, the Sorting Order organises the information content into numerical or alphabetical order. The applications of both the orders make the report more meaningful, thereby more useful to the users.

A query is a statement that gives refined data according to the conditions and specifications of the user. It can retrieve the desired data from the multiple data simultaneously and can also update or delete multiple records at the same time. It is a link between the interrelated tables, forms, etc.
Accounting information that is presented in an accounting report is generated by creating and executing various queries under DBMS. A query communicates to Access the kind of information which we require to extract from one or more interrelated tables.
The following are the basics of creating queries in the MS-Access with a set of inter-related data tables.
As Relational DBMS stores data in different tables, so there is no or minimum data redundancy. But a complete view of data that is stored across various tables can be achieved only by executing queries based on SQL. A query shows the records containing fields from across a number of data tables. A SQL statement has a number of parameters such as data definition, query, update, capability to define user-oriented views of database, specify security and authorisation and defining integrity constraints.
Example:
The procedure of creating queries for inter-related data tables using SQL statements is explained below by the help of an example.
SELECT Code, Name, Sum (Amount)
FROM Vouchers INNER JOIN Accounts.
ON (Accounts, Code = Voucher Debit)
GROUP BY Code, Name.
In this query, the ‘Vouchers’ table has been joined with ‘Accounts’ table on the basis of ‘Code’ fields of ‘Accounts’ and ‘Debit’ field of vouchers. The result record set has been grouped on the basis of Code and Name of Accounts.

While forming the SQL statement, the following clauses are normally used for generating information (or Select) queries:

1. SELECT: This clause is used to specify the fields to display data or information. Consider the following SQL statement segment:

SELECT Code, Name, Amount

The fields Code, Name and Amount after SELECT clause indicate the data items to be displayed by the query statement.

2. FROM: This clause is meant to indicate the source of data in terms of tables or queries or a combination of both. Two tables are joined by specifying a JOIN clause based on a condition of Join. There can be three types of Join: Inner, Left and right.
3. INNER: This Join clause is meant to display only exactly matching records between two data sources. Consider the following SQL statement segment:

FROM Accounts INNER JOIN AccountType

ON ( CatId = Type)

In the above statement, only those records of Accounts and AccountType table constitute the source of query data, which match exactly on CatId = Type.

4. LEFT: With this Join, all the records in the primary table in the relationship are displayed irrespective whether there are matching records in the related table or not. Consider the following SQL statement segment :

FROM Accounts LEFT JOIN Account Type

ON ( CatId = Type)

In the above statement, all records of AccountType along with matching records of Accounts table constitute the source of query data. The matching condition is CatId = Type.

5. RIGHT: With this Join, all the records of related table in the relationship are displayed irrespective whether there are matching records in the primary table or not. Consider the following SQL statement segment

FROM Accounts RIGHT JOIN AccountType

ON ( CatId = Type)

6. WHERE: This clause in SQL statement is used to provide the condition to restrict the records to be returned by query. The resultant records of query must satisfy the condition which is specified after WHERE clause. This is meant to filter records returned by the query.

7. ORDER BY: This clause is meant to specify the order in which the resultant records of query are required to appear. The basis of ordering is determined by the list of fields specified after the order by clause. Consider the following SQL statement segment:

ORDER BY Type, Cod

8. GROUP BY: The group by clause is used in the SQL statement to enable grouping of records for creating summary query. The fields after GROUP BY clause constitute the basis of grouping for which summary results are obtained. Consider the following SQL statement:

A report in Access is designed using seven sections which taken together constitutes the structure of report design. It is not necessary that every report designed in Access must have all the sections that have been described below:"

1. Report Header: Report header appears at the top of the report and may include title and other relevant information pertaining to the report.
2. Page Header: Page header appears at the top of every page of the report. It may include a uniform title to indicate that the page belongs to a particular report.
3. Group Header: The group header and footer are available in a report only if the sort order and grouping levels are also defined on the basis of a field of data source. This is because Group Header and Footers are properties of the field that are used for defining the sort order. Depending on grouping level, the group header appears at the top of each report group. A set of report pages constitutes a report group. Each group level of report contains a separate group header.
4. Details: The details section, which is also called the main body of a report, contains data from tables or queries that provide the record source to a report. This section is most important as it consists of the main information content of a report.
5. Group Footer: The group footer appears at the bottom of each grouping level and may contain summaries or sub-totals for the grouped data.
6. Page Footer: The page footer appears at the bottom of each page of the report and is meant to include page numbers, date and time of report generation.
7. Report Footer: The report footer appears once on the last page of the report to include summaries or totals for all data of the report.

A report design can be refined using the following ways:

1. Adding Dates and Page Numbers: In the design mode, the page footer of a report contains two unbound controls- ‘current date’ and ‘current page number of the total number of pages’. The current date can be retrieved from the RTC using ‘Now ( )’ function. The page number can be added by clicking ‘Insert % page’ numbers from the menu bar to open the page numbers dialogue box. With this, the designer can specify the format, position and alignment. Similarly, the date and time can also be added by clicking ‘Insert % date and time’ from the menu bar to open the Date and Time dialog box.

2. Adding and Deleting Report Controls: After designing the report, one can add or delete additional report controls by the same procedures as of forms.

1. Open the report in the design view and click ‘Field List’ button on report design tool bar.
2. Now, drag the field into an appropriate section of the report, so that the field appears with both label and text box control.
3. One can also delete a field by selecting the control and pressing the ‘Delete’ key.

3. Conditionally Formatting the Report Controls: With the help of Conditional Formatting, the designer can apply special text formats that depend on the value of a field. Such a formatting helps to draw the attention of the users or readers of the report to some values of particular interest.

The following steps are followed to create a Conditional Formatting.

1. Open report in the design view and select a control and click at ‘Format’ on the menu bar.
2. Now, click at ‘Conditional Formatting’ and provide the necessary conditions for formatting.
3. Conditional Formatting can also be deleted by reopening the same dialog box and clicking the ‘Delete’ button.

4. Grouping and Sorting of Data: The Grouping Levels on one hand organises the information content of a report into various categories, while on the other hand, the Sorting Order organises the information content into numerical or alphabetical order. The applications of both the orders make the report more meaningful, thereby more useful to the users.

To create a query using design view method, the following steps are followed
Step1: Select ‘Queries’ from the object list given on the left hand side of the database window.
Double click at ‘Create Query by using Design View’ given on the right hand side.
Step 2: With this, a ‘Select Query’ and ‘Show Tables’ window is displayed. The ‘Select Query’ window is divided into two panes namely, the upper pane (displays the data sources) and the lower pane also known as Query by Example (QBE) (displays column and row). The column of the lower pane displays the data sources. The row of the lower pane displays the field name, table (or query), sort order, whether the selected field is shown in the query results or not and also the criteria that have been applied to the fields to restrict the query results.
Step 3: Click at the ‘View’ item of the Menu Bar % Total and then % Table Names.
Step 4: Click at the ‘Field Row’ of the first column of the QBE and select the fields to be included in the query. The process is repeated for the subsequent columns of the QBE to include more fields in the query. This process of selection contains the data items that are to be displayed by the SELECT clause of the SQL statement.
Step 5: Now, in accordance to the selection of the fields, the name of the table or query is displayed. Such tables or queries constitute the data sources shown after the FROM clause of the SQL statement.
Step 6: Click at ‘Row of the low pane’ to specify the group by clause and aggregate functions to create a summary of the query is created.
Step 7: Click at ‘Row of the low pane’ to specify the sort order (Ascending or descending). The selected fields for sort order are shown after ORDER BY clause of the SQL statement in which ascending order is a default choice.
Step 8: Click at row to display the selected field in the query result.

1. Click at row of the grid to specify the criteria to limit the records to be displayed by the query being created. The specified criteria result is shown after the WHERE clause of the SQL statement.
2. Click File % Save (or Press Ctrl + S) to save the query. Now, a dialog box appears that asks the user to specify the name of the query being created.

To produce a ledger, it is essential to retrieve a set of processed data records providing information on Code (Account Number), Name of Accounts, Particulars, Date, Debit Balance and Credit Balance with reference to each particular account.
In order to get all this information, it is necessary to make certain SQL statements and save them as Query. Suppose, we have saved the last query as ‘query L’. In Access, a ledger account can be created by taking the following steps in the design view method.

1. Select ‘Reports’ from the ‘Object’ list in the Database window.
2. Click ‘New’ button so that ‘New Report’ window is displayed.
3. Select ‘Design View’ and ‘Query L’ from the combo control.
4. Click ‘OK’. With this, the access responds by displaying a blank report design divided horizontally into three sections- Page Header, Detail and Page footer. Also, a list of available fields of Query L is provided for embedding on to this blank design.
5. Click at ‘Properties’ of the report and select ‘Data’ tab to define the recorded source as Query L. This displays a list of available fields of Query L.
6. Right click at any part of the report design and choose Report, Page Header and Footer. With this, the Access responds by providing two more sections- Page Header and Pager Footer.
7. Click at the ‘icon’ of the toolbar and pick up a label control to be placed at the Page Header section and assign its caption.
8. Select all the fields of Query L by clicking at every field, while keeping the ‘Ctrl key’ pressed. Drag and drop the selected field on the ‘Details Section’.
9. Select the total control of all the six fields by clicking at each, while keeping the ‘Shift key’ pressed. Right click at the ‘Select Label Control’ and choose ‘Cut’. Now, place the mouse at the Page Header Section and ‘Paste’ these controls.
10. Choose the ‘Properties’ provided by the Access.
11. Align the text controls in the ‘Details Section’.
12. Select the ‘Text Controls’ and ‘Amount Field’ and modify their properties. Hence, the required ledger in access is created.

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.

1. To find the total amount by which the accounts have been debited: The transacted accounts in design of Model-II have been stored in AccCode of Vouchers Main and Code of VouchersDetail. The following SQL statement is formed to generate the relevant information from Vouchers Details.

SELECT Code, Sum(amount) AS Total

FROM vouchers Main INNER JOIN vouchers Details ON

Vouchers Main. Vno = Vouchers Details. Vno

WHERE Type = 0

GROUP BY Code:

Similarly, the following SQL statement is formed to generate the required information from Vouchers Main table.

SELECT AccCode As Code, sum(amount) AS Total

FROM vouchers Main INNER JOIN vouchers Details ON

Vouchers Main.Vno = Vouchers Details. Vno

WHERE Type = 1

GROUP BY AccCode:

Both the SQL statements are meant to extract similar sets of records, but from two different sources. Therefore, the resultant record set of these SQL statements have been horizontally merged using UNION clause as shown below:

SELECT Code, sum[amount) AS Total

FROM vouchers Main INNER JOIN vouchers Details ON

Vouchers Main. Vno = Vouchers Details. Vno

WHERE Type = 0

GROUP BY Code

UNION ALL

SELECT AccCode As Code, sum(amount) AS Total

FROM vouchers Main INNER JOIN vouchers Details ON

Vouchers Main. Vno = Vouchers Details. Vno

WHERE Type = 1

GROUP BY AcCode:

The above SQL statement is saved as Query 101 for its subsequent use. The total of debit amount in this query represents the Total with positive amounts.

2. To find the total amount by which the accounts have been credited: In order to ascertain the total amount by which every transacted account has been credited, a query similar to that in (a) need be formed. This is achieved by substituting Debit field in SELECT and GROUP BY clause by Credit field and the sum of amount generated by sum(Amount) is multiplied by-l so that the final amount assigned to Total field is always negative. Accordingly, the following SOL statement is formed:

SELECT Code, sum[amount)*-1 AS Total

FROM vouchers Main INNER JOIN vouchers Details ON

Vouchers Main. Vno = Vouchers Details. Vno

WHERE Type = 1 GROUP BY Code, Amount

UNION

SELECT AccCode As Code, sum(amount)*- 1 AS Total

FROM vouchers Main INNER JOIN vouchers Details ON

Vouchers Main. Vno = Vouchers Details. Vno

WHERE Type = 0 GROUP BY AccCode, Amount:

In the above SQL statement, the sum of amount has been multiplied by -1 to ensure that the amount of credit is always negative just as amount of debit is taken as positive. This query is saved as Query 102 for its subsequent use.

3. 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 Query 101 and Query 102 to ensure that the debit and credit amount with respect to each account becomes available for generating the net amount. Accordingly, the following SOL statement is formed.

SELECT*

FROM Query 101

UNION Select*

FROM Query 102:

The above SQL statement causes horizontal merger of record sets returned by Query 101 and Query 102. This SQL Statement is saved as Query 103 for its subsequent use in next query.

4. 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 Query 103 instead of Query 03.

SELECT Code, Sum(Total) AS Net

FROM Query 103

GROUP BY Code

This query is saved as Query 104 for its subsequent use in generating a record set, giving details of information for trial balance.

5. 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 05 (designed and discussed in (e) of Model-1). Accordingly, the following so' statement is formed by changing the source of data from Query 05 Query 105 as shown below:

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 104 AS a

Accounts AS b/

WHERE a.code = b.code:

In above SQL statement, the results of Query 104 and data stored in accounts table has been used. This SQL statement is saved as Query 105 for providing source of information to Trial Balance Report.

Trial Balance 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 (1) and (g) are required.

6. 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. Catld FROM Accounts

INNER JOIN AccountType ON

Accounts. Type = Account type.Catld:

This SQL statement is saved as Query 106 for its subsequent use in next query.

7. 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 Query 105 is modified by substituting Accounts table with Query 106 to form the following Statement.

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, Category. Catld

FROM Query 104 AS a. Query 106 AS b

WHERE a.code = b.code:

This SOL statement is saved as Query 107 to provide information details for designing trial balance with grouping and sorting of the accounts.

The accounting reports are the physical form of accounting information. They act as a summary of all the relevant facts and information related to a particular event. The accounting reports are basically the end (or final) reports depicting the performance of an organisation during an accounting period. They show the information that is acquired from processing and transforming the data in an organised manner. The accounting reports display the information content in such a manner that the users can understand them without any ambiguity. These reports form the basis of the decision making process as they minimise the risks associated with uncertainty. Ledgers, Trial Balance, Cash/Bank Book, Financial Statements etc. are some of the examples of accounting reports. An accounting report can be useful, if it has five basic characteristics, namely, Relevance, Timeliness, Accuracy, Completeness and Summarisation.
The accounting reports can be classified into two following broad categories.

1. Programmed Reports: These reports contain information useful for decisionmaking situations that the users have anticipated to occur. There are two types of reports within this report type: Scheduled and On demand.

1. Scheduled Reports: The reports, which are produced according to a given time frame, are called scheduled reports. The time frame may be daily, weekly, monthly, quarterly or yearly. Some examples of scheduled reports are: Trial Balance, Ledger, Statement of Cash Transactions (Cash Book), Statement of Ageing Accounts, Closing Stock Report, Profit and Loss Account and Balance Sheet, etc.
2. On Demand Reports: The reports, which are generated only on the triggering of some event, are called On demand reports. Some examples of On demand reports are a Customer’s Statement of Account, Inventory Re-order Report, Stock in hand Report for a Selected Group of items, etc.

2. Casual Reports: There are reports, the need for which is not anticipated, the information content of which may be useful but casually required. These are adhoc reports and are generated casually by executing some simple queries without requiring much of professional assistance. As opposed to programmed reports, casual reports are generated as and when required.

Process of Creating Reports
The process of generating accounting reports in Access involves three steps: designing the report, identifying the accounting information queries, and finally creating an accounting report by using such queries.

1. Designing the Report: Every report is expected to meet certain objectives of reporting for which it is designed and developed. It should not be too big so as not to be read at all or too small so as to conceal certain vital information of importance that is expected to facilitate decision-making. Objective-oriented reporting means designing the report in such a manner as to meet the pre-conceived objectives in view.
2. Identifying Accounting Information Queries: A number of SQL statements are written in such a manner that each successive SQL relies on the results of the preceding SQL statement and refines its results by using fresh data (or information) from existing data tables (or queries).
3. Using the Record Set of Final SQL: The record set of final SQL that relies upon preceding SQL statement, is collection of report-oriented information. This record set need be embedded in the report being produced.