Loading....
Coupon Accepted Successfully!

 

Question-1

State what do you understand by accounting reports.

Solution:
Accounting information, generated by processing accounting data is gathered to generate as accounting report. The output of the computerized accounting system are called accounting reports.

Question-2

What do you mean by programmed or casual reports?

Solution:
These reports contain useful and decision – making information that the user wants to generate. In this context, the reports which are produced according to a given time frame, ie., daily, weekly, monthly, quarterly or yearly, are known as Scheduled Reports. Trial Balance, Ledger, Statement of Cash Book, Closing Stock Report, Profit and Loss Account and Balance Sheet are scheduled reports. And the reports which are generated only due to need of some event are called on Demand Reports.

Question-3

With the help of an example, briefly state the meaning of parameter queries.

Solution:
This type of query, when executed, displays a dialog box in which you can enter the criterion for retrieving data. This enables you to customize the output of your query based on the value that you enter while executing it.

PARAMETERS Account Name

SELECT Name

FROM Accounts

WHERE Code = Account No

Question-4

Briefly state the purpose of functions in SQL environment.

Solution:
The SQL aggregate functions are to find out the sum of a set of values, minimum and maximum of value of set with respect to field or query expression, count the number of records returned by a query and first and last record of a value set with respect to a field or query expression.

Question-5

Briefly explain in steps the method of creating a query, using wizard.

Solution:
1. Using Design View – The Design view enables you to create a query from scratch. It enables you to specify the tables, fields and criteria of a query.

2. Using Query Wizard – The Query Wizard displays a series of dialog boxes in which you can specify the tables and fields that you want to include in the query

Question-6

List the structure of a good report created in Access.

Solution:
1. Using the Wizard - The Report Wizard in Access creates a report automatically, based on the field, layout and styles that you select.

2. Using the Design View - The Design view enables us to create a report from scratch. It is easier to create a report using the Report Wizard and later use the Design view to make the required modifications to the report.

Question-7

List the ways to refine the design of a report.

Solution:
Adding dates and page numbers
  Adding and deleting report controls
  Conditionally formatting report controls
  Grouping levels and sorting order

 

Question-8

Briefly explain the purpose of grouping and sorting of the data as a means to refine a report.

Solution:
The main purpose of grouping is to organize the information content of a repot into categories. Sorting order is meant to arrange such information content into numerical or alphabetical order. With groupings the sorting applies to each individual group. The grouping and sorting of information, when applied together, makes the report more meaningful and hence is useful to the person who use the report.

Question-9

What do you understand by saving a report as snapshot?

Solution:
A report is saved as a snapshot so that it can be viewed by others without the help of MS Access.

Question-10

State the procedure for creating trial Balance in MS Access.

Solution:
A set of processes data records each of which provides information on Code, name of Account, Debit Balance and credit Balance with reference to each account is retrieved. In order to find out the net balance corresponding to every account along with its identity, the following steps are taken.

a) To find the total amount by which every account has been debited

b) To find the total amount by which every account has been credited

c) To find a collective record set of accounts with their debit and credit totals

d) To find the net amount with which every account has been debited or credited and

e) To find the record set which consists of Account code, name of Account, Debit and credit Amount.

 

Question-11

Discuss the concept of accounting reports? Explain the three steps involved in creating such reports.

Solution:
Every report consists of ‘information’, which is different from ‘data’. Data processing leads to data transformation and when this processing is in accordance with decision usefulness, it is called information. Information generation is the process of compiling, arranging, formatting and presenting information to the users. A report is prepared with a definite objective. Every report is collection of related information for a particular need and purpose and must meet the twin objectives of reporting : one to reduce the level of uncertainty that is faced by a decision-maker; second to influence the behaviour (or positive actions) of the decision-maker. Accordingly, accounting information, generated by processing accounting data is gathered to generate an accounting report. An accounting report, therefore, is the physical form of accounting information. Useful accounting information, regardless of its physical form, must have five characteristics: relevance, timeliness, accuracy, completeness and summarization. An accounting report, in order to be useful, must display information content in such a manner as to give confidence to the user, influence his behaviour and prompt him to take positive actions. Reports, which do not meet the above stated objectives, lack or do not have sufficient information content, have no value.

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.

(i) 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.

(ii) 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).

(iii) 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.

Question-12

Discuss with a set of inter-related data tables, the basics of creating queries in MS Access?

Solution:
A complete view of data stored access various tables is achieved only by executing queries based on SQL. A query is capable of displaying data records contained in many data tables. There are many types of queries in Access that are used to generate reports which are given below-

1. Simple Query - This type of query enables us to identify a field from the database. For example, to find amount of transactions record contained in voucher no. - 01, we can use the following SQL statement:

SELECT Amount

FROM Vouchers

WHERE Voucher no – ‘01’

In the above SQL statement, the SELECT statement is meant to specify the field to be selected, FROM clause specifies the source of data, and WHERE clause filters the records matching the condition that Voucher No = ‘01’.

2. Parameter Queries - This type of query is used to identify a set of records which contains the same criteria. Consider, to search voucher records of a particular account from the Accounts database, we can use the following SQL Query:

PARAMETERS Account Name

SELECT Name

FROM Accounts

WHERE Code = Account No.

3. Summary Queries - This query type is of particular importance in accounting because it enables us to create accounting reports based on summarization of transaction data.

SELECT Code, Name, Sum (Amount)

FROM Vouchers INNER JOIN Accounts

ON (Accounts, Code = Vouchers.Debit)

GROUP BY Code, Name

Question-13

Describe in steps the design view method to create a query in MS Access?

Solution:
To create Queries, we have to take the following steps:

1. Click Queries in the Objects Bar in the Database window.

2. Double click the Create Query in Design view option.

At this point, show Table Dialog box is displayed:

1. Click Tables to opt for the name of the table that you want to add to the query.

2. Click the Add button

3. Repeat these steps till you add all the required tables.

4. Click the close button.

At this point, show Table dialog box is closed and the Select Query Window is displayed.

1. Click the name of the table from which you want to add a field to the query in the dropdown list box in the Table row.

2. Click the name of the field from which you want to use in the query from the dropdown list box in the Field row

3. Click the show option if you want to display the field in the output of the query.

4. If there is a criteria for the field, type it in the Criteria Field.

5. Type the name of the query in the Query Name box.

6. Click OK

 

The query is saved. Show the dialog box giving results.

 

Question-14

Discuss the SQL view method of creating a query?

Solution:
SQL View Method to create a Query: It is also a method of creating Query in Access. This type of SQL Query statement is written by using the keyboard. While forming the SQL statement, the following clauses are normally used for generating queries:

a. SELECT - This clause is used to specify the fields to display data which were given after SELECT in the SQL Statement. SELECT Vno., Name, Amount

b. INNER - This clause is used to display matching records between two data tables. To make relation between two data tables, we have to type Join after the clause. For example, if we have to select those records of Debit Voucher and credit Voucher table whose category ID and TYPE respectively are the same, we can use the following SQL statement.

FROM Credit voucher INNER JOIN Debit Voucher

ON (CatId = Type)

c. LEFT - This clause in a SQL query statement enables the display of matching records of those tables along with the records (matched and unmatched) given in the first table in the statement. Consider the following example:

FROM Credit Voucher LEFT JOIN Debit Voucher

ON (CatId = Type)

This statement will display all records of Credit Voucher along with the matching records of Debit Voucher.

d. RIGHT - This clause is the opposite of the LEFT Clause and displays all records of Debit Voucher along with the matching records of Credit Vouchers, the statement is as given below:

FROM Credit Voucher RIGHT JOIN Debit Voucher

ON (CatId = Type)

e. ORDER BY – By using this clause in SQL query statement, we get the result in the order in which the resultant records of the query are required to appear.

ORDER BY Type, Code

f. GROUP BY - This clause is used in the SQL statement to provide grouping of records for creating a summary. For example,

SELECT Credit, Sum (Amount)

FROM Vouchers

GROUP BY Credit

This SQL statement results in display of the total amount by which every transaction has been credited.

 

Question-15

Describe the ways to refine the design of a report.

Solution:
The design of the report created by any of the methods described above may be improved upon by making the following additions and modifications to the report. For this purpose, an existing report is opened in design mode.

Adding Dates and Page Numbers: When an existing report is opened in design mode, the page footer of the report contains two unbound controls: the current date and current page number of total number of pages. Both the controls may be customised according to the requirement of the designer. The date control uses = Now() function to retrieve the current date from RTC of computer. The format of date may be modified by selecting General date, Medium date, Short date or Long date from format property of this control. Further, when a report is created using design view method, the date and/ or time and also the page numbers may be added to any of its part. The date and time is added by clicking Insert % date and time from the menu bar to open the Date and Time dialog box. After selecting and specifying the desired preferences regarding date and time, click OK to find that a text control with chosen date and time preferences is added at the top of active report section. This added text control containing date and time may be dragged and dropped in any part of the report as per requirement. Similarly, the page number is added by clicking Insert % page numbers from the menu bar to open the Page numbers dialogue box. This dialogue allows the designer to specify the format, position and alignment. The two formats are: Page N (for example Page 1) and Page N of M ( for example Page 1 of 10). The position to specify is either Top of Page (header) or Bottom of Page (footer). Possible alignment, which may be specified are Centre, left, right, inside and outside.

Adding and Deleting Report Controls: After a report has been designed, additional report controls may be added or deleted by the same procedure as applicable to forms. Clicking tool bar icon opens report design tool bar, which contains a set of useful controls.

(a) After opening the report in design mode, click Field List button on report design tool bar. This results in opening the field list window.

(b) Drag the field into an appropriate section of the report. The field appears with both label and text box control. The label part gives a constant field heading while the text part provides different values of the field. These two parts are accordingly placed at the appropriate sections of the report.

(c) A field control may be deleted by selecting the control and pressing the Delete key.

Conditionally Formatting Report Controls: The conditional formatting of text boxes and combo boxes in reports can be achieved in the same manner, as it applies to Forms. The conditional formatting allows the designer to apply special text formats that depend on the value of field. This facility is a useful tool to draw the attention of user or reader of report to some values of particular interest, such as amounts exceeding certain limit or unexpected balances in some accounts. In order to create a conditional formatting, following steps are required:

(a) Open the report in design view.

(b) Select a control and click at format on menu bar, followed by conditional formatting.

(c) Provide the necessary conditions for formatting to occur in the same manner as already discussed while applying conditional formatting to design of Forms.

(d) The conditional formatting is removed by re-opening the same dialog and clicking at delete button.

Grouping Levels and Sorting Order: The purpose of grouping is to organize the information content of a report into categories. Sorting order is meant to arrange such information content into numerical or alphabetical order. With groupings the sorting applies to each individual group. The grouping and sorting of information, when applied together, make the report more meaningful and therefore useful to the user of the report. In order to specify the grouping and sorting order, following procedure is adopted.

(i) Click at Sorting and Grouping icon of Report Design Tool bar (This icon is located next to icon for tool box). Immediately, Access responds by displaying the following Sorting and Grouping dialogue box.

(ii) The LHS of this dialog box provides a list of fields or expressions that are to be used for grouping and sorting. In the above dialog box, Type field of Accounts has been chosen as the basis of grouping the information content of trial balance. The group header and footer property is set to Yes to indicate that there is separate header and footer for each group of accounts in trial balance.

 

Question-16

Explain the data base design for Model-I for producing the receipts the series of SQL statements for producing the payment side of cash book for Model-II.

Solution:
The following series of SQL statements retrieve a record set for producing trial balance when database design for Model-I is used.

(a) To find the total amount by which the accounts have been debited: In order to ascertain the total amount by which every transacted account has been debited, the SELECT clause need to have two fields: one code to identify the transacted account and another to generate the total by which such account has been debited. This is achieved by using Debit field of Vouchers table and finding the sum of amount corresponding to each of the transacted accounts. The FROM clause relies upon Vouchers table to get the data source. The GROUP BY clause specifies the field on the basis of which grouping of record set is formed. This grouping is necessary in SQL when aggregate query is used to generate summary information. The summing of amount is obtained by using aggregate function, Sum( ). This function, as already explained, uses a field with data type Number, as an input argument and returns its sum as output. Accordingly, the following SQL statement is formed:

SELECT Debit AS Code, Sum (amount) AS Total

FROM vouchers

GROUP BY debit;

In the above SQL statement, the GROUP BY clause retrieves the rows of vouchers table accounts-wise because the debit field refers to account code. As a result, the Sum( ) computes the sum of amount of a particular debit account and reports against Debit account of SELECT clause. This SQL statement is saved as Query 01for its subsequent use. The total of debit amount in this query is given by Total field with positive amounts.

(b) 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, 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 -1 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:

SELECT Credit AS Code, Sum(Amount)*(-1) AS Total

FROM vouchers

GROUP BY Credit;

This SQL statement is saved as Query 02 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 01 and Query 02.

SELECT*

FROM Query 01

UNION SELECT*

FROM Query 02 ;

This SQL statement is saved as Query 03 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 03 as the data source. The sum of Total for each Code of data source, provided by Query 03, results in computing net amount for every account. Accordingly, the following SQL statement is formed to generate a list of account codes with their respective balances: positive or negative.

SELECT Code, Sum (Total) AS Net

FROM Query 03

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 02, the total of credit amount has been made to appear as negative. This query is saved as Query 04 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>0,a.Net,null) AS Debit,

IIF (a.Net<0,abs(a.Net), null) AS Credit

FROM Query 04 AS a, Accounts AS b

WHERE a.code = b.code ;

In the above SQL statement, the results of Query 04 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>0,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<0,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 04 and Accounts table respectively. This SQL statement is saved as Query 05 for providing the necessary information content for Trial Balance Report.

Question-17

Describe the series of SQL statements to produce trial balance data base design for Model-II is used.

Solution:
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) 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 VouchersDetails.

SELECT Code, Sum(amount) AS Total

FROM vouchersMain INNER JOIN vouchersDetails ON

VouchersMain.Vno = VouchersDetails.Vno

WHERE Type = 0

GROUP BY Code;

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

SELECT AccCode As Code, sum(amount) AS Total

FROM vouchersMain INNER JOIN vouchersDetails ON

VouchersMain.Vno = VouchersDetails.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 vouchersMain INNER JOIN vouchersDetails ON

VouchersMain.Vno = VouchersDetails.Vno

WHERE Type = 0

GROUP BY Code;

Accounting System using DBMS

UNION ALL

SELECT AccCode As Code, sum(amount) AS Total

FROM vouchersMain INNER JOIN vouchersDetails ON

VouchersMain.Vno = VouchersDetails.Vno

WHERE Type = 1

GROUP BY AcCode;

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

(b) 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-1 so that the final amount assigned to Total field is always negative.

Accordingly, the following SQL statement is formed:

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

FROM vouchersMain INNER JOIN vouchersDetails ON

VouchersMain.Vno=VouchersDetails.Vno

WHERE Type=1 GROUP BY Code, Amount

UNION

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

FROM vouchersMain INNER JOIN vouchersDetails ON

VouchersMain.Vno=VouchersDetails.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 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 03.

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 05 (designed and discussed in (e) of Model-I). Accordingly, the following SQL statement is formed by changing the source of data from Query 05 to Query105 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 Query104 AS a,

Accounts AS b,

WHERE a.code = b.code;

Question-18

Using Model-III discuss the series of SQL statements to produce a trial balance up to a particular date.

Solution:
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 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 (a) and (b) are required.

(a) 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 106 for its subsequent use in next query.

(b) 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>0,a.Net,null) AS Debit, IIF(a.Net<0,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.





Test Your Skills Now!
Take a Quiz now
Reviewer Name