NCERT Solution (Part - 1) - Accounting Database for Database Management System | Accountancy Class 11 - Commerce PDF Download

Page No 629:

Short Answers
Question 1: State what you understand by accounting reports.
Answer : 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.

Question 2: What do you mean by programmed or casual reports?
Answer : Accounting reports can be classified into two broad categories- Programmed Reports and Casual Reports.
1. Programmed Reports- These are those reports that contain information, which can
be utilised by the users in the situations that they expect to occur in the near future.
a) Scheduled Reports- These are the reports that are prepared for a specific period of
time. These reports can be prepared regularly on daily, weekly, quarterly, monthly or
yearly basis. Trial Balance, Ledgers, Statements of Cash Transactions, etc. are some of
the examples of Scheduled Reports.
b) On-Demand Reports- These are the reports that are prepared as and when required
or on the happening of some particular event. Customer's Statement of Accounts,
Inventory Re-order Report, Stock Purchased Report, etc. are some of the examples of
On-Demand Reports.
2. Casual Reports- These are the reports that contain information which may be useful
but are only casually required. The users do not anticipate their need. These are also
known as ad hoc reports and can be generated randomly without much professional
assistance.

Question 3: With the help of an example, briefly state the meaning of parameter queries.
Answer : A Parameter Query is a query where for selecting a set of records, the user is asked to enter parameters or criteria through an input box. A Parameter Query is useful when the same query is to be repeated with different criteria. In short, it implies the execution of same set of queries with different requirements.
For example, the same set of queries is required for different accounts code, while
extracting the information to prepare ledger. Let us consider the given below SQL
statement.
PARAMETERS Accounts Name Text (145)
SELECT Name
FROM Accounts
WHERE Code = AccountNo.
In the above query, PARAMETERS clause is used to represent the variable AccountNo.
This SQL statement requires the user to provide the different values of Account No.

Question 4:  Briefly state the purpose of functions in SQL environment.
Answer : 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.
a. IIF- These functions are used to provide a value to the field from a mutually exclusive set of values.
b. 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.
c. Val- These functions are used to provide numbers contained in a string as a numeric value of appropriate type.

Question 5: Briefly explain in steps the method of creating a query, using wizard.
Answer : 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' and specify the name of the query being created % 'Finish' to save and
execute the query. The results of the query created are displayed in the datasheet view.

Question 6: List the structure of a good report created in Access.
Answer : A good report designed in the access must have the following sections -
1. Report Header- It appears at the top of the report and includes title and other relevant information related to the report.
2. Page Header- It appears at the top of every page of the report and includes a uniform title to indicate that the page belongs to a particular report.
3. Group Header- It appears only when the report contains the sort order and grouping levels.
The sort order and the grouping headers are defined on the basis of a field of data source.
Group headers appear at the top of each report group. There are separate group headers for each group level.
4. Details- It is also regarded as the main body of a report. It contains data from tables or
queries that provide the record source to a report. This section consists of main information content of a report, hence is considered to be the most important section.
5. Group Footer- It appears at the bottom of each grouping level and contains summaries or sub-totals for the grouped data.
6. Page Footer- It also appears at the bottom of each page of the report and shows page
numbers, date and time of report generation.
7. Report Footer- It appears on the last page of the report and includes summaries or totals for all the data of the report.

Question 7: List the ways to refine the design of a report.
Answer : 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.

Question 8: Briefly explain the purpose of grouping and sorting of the data as a means to refine a report.
Answer : The purpose of grouping is to arrange the information content of a report into various categories, while on the other hand, the main purpose of the sorting order is to organise 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.

Question 9: What do you understand by saving a report as snapshot?
Answer: Saving a report as a snapshot file enables the users to view it without the help of
access. When a report is saved as a snapshot, a high quality picture image of each page of the report is created with Adobe Acrobat Software. With this the users can view the report and print any of its pages. However; the users cannot modify the contents of the reports in a snapshot file.

Question 10: State the procedure for creating ledger in MS Access.
Answer : 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.

Long  Answers

Question 1: Describe and discuss the procedure of creating the receipts side of a cash book.
Answer : 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 of 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.
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.

Question 2: Discuss the concept of accounting reports? Explain the three steps involved in creating such reports.
Answer : 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 are those reports that contain information, which can
be utilised by the users in the situations that they expect to occur in the near future.
a) Scheduled Reports- These are the reports that are prepared for a specific period of
time. These reports can be prepared regularly on daily, weekly, quarterly, monthly or yearly basis. Trial Balance, Ledgers, Statements of Cash Transactions, etc. are some of the examples of Scheduled Reports.
b) On-Demand Reports- These are the reports that are prepared as and when required
or on the happening of some particular event. Customer's Statement of Accounts,
Inventory Re-order Report, Stock Purchased Report, etc. are some of the examples of
On-Demand Reports.
2. Casual Reports- These are the reports that contain information which may be useful but are only casually required. The users do not anticipate their need. These are also
known as ad hoc reports and can be generated randomly without much professional assistance.
Process of Creating Accounting Reports
In Access, the generation of accounting reports involves the following three steps.
Step 1: Designing the Report
A report must be designed in a manner, so as to meet certain objectives of reporting. It
should not be either too long as not to be read or too short so as to conceal certain
important information that is expected to help in the decision making process.
Step 2: Identification of Accounting Information Queries
Many SQL statements are written in such a manner that each successive SQL depends
on the results of the preceding SQL statement. It then, refines its results by using fresh data from the existing data tables.
Step 3: Using the Record Set of Final SQL
The record set of final SQL is a collection of report-oriented information. It relies upon
the preceding SQL statement. This record set must be included in the report being
produced.

Question 3: Discuss with a set of inter-related data tables, the basics of creating queries in MS Access?
Answer : 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 interrelated 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.

Question 4: Briefly explain the set of SQL statements to produce the receipts side of a cash book for Model-I.
Answer : 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.
(a) A code to identify the account
(b) 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