NCERT Solutions for Class 11 Accountancy Chapter 15 Accounting System Using Database Management System

1. State what do you understand by accounting reports?

Ans: Accounting reports are summaries of financial data produced from a company's accounting records. Depending on the need and purpose for the reports, they might be complete, customizable, and detailed. For instance, a detailed sales analysis by region, or the earnings of a specific product line. Financial statements are another term for accounting reports. The income statement, balance sheet, and statement of cash flows are the most common statements.

2. What do you mean by programmed or casual reports?

Ans: Programmed reports provide data that can be used by users in a hypothetical situation that will occur shortly. Casual reports are reports that contain information that can be used but are only required on occasion. Users are not in charge of determining their needs. Ad hoc reports are another name for programmed reports.

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

Ans: To begin with, queries in a database system are thought of as the system's workhorse. These are intended to give a method for correlating any tables and retrieving information depending on specified database circumstances or criteria. Parameter Query refers to a query that asks the user for specific information, which is subsequently used by the query in a criterion or expression format.

When extracting information to prepare a ledger, for example, the same set of queries is necessary for different accounts codes. Let's take a look at the SQL query below.

Accounts Name Text Parameters (145)

Select Name

From Accounts

Where Code = Account no.

The Variable Account no Is Represented In The Above Query Via The Parameters Clause. This Sql Statement Needs The User To Specify Several Account No. Values.

4. Briefly state the purpose of functions in the SQL environment.

Ans: A Function is a database object that exists in the SQL server. It is a set of SQL statements that accept only input parameters, conduct actions organized, and return the result. 

1. Domain Aggregate Functions: These functions are used to do calculations based on data in a table or query's field. The syntax for a domain aggregate function is as follows: DFunction (Fld Name, Tbl Name, or Qry Name, “Srch Cond”) is a Domain Aggregate Function (DFunction).

The name of the field to be searched is represented by Fld Name.

The name of the table or query that contains the a for a mentioned field is represented by Tbl Name or Qry Name.

Srch Cond stands for Search Condition, which is used to find a relevant record.

2. SQL Aggregate Functions are utilized in SQL statements that provide the underlying record source for forms and reports. When used in a SQL query, all of these functions require the GROUP BY clause.

3. Other Functions- In addition to the Domain Aggregate Function and SQL Aggregate Function, SQL also includes a number of other functions. These are listed farther down.

IIF - These functions are used to give a field a value from a set of mutually exclusive values.

Abs - These functions return an absolute value in exchange for a numeric value.

Val - These functions return a numeric value of the specified type for numbers contained in a string.

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

Using the query wizard to create a query:

Ans: There are two ways to generate a query: one is to use the query wizard, and the other is to use the query editor.

• Select Query Wizard from the drop-down menu.

• Select New Query from the drop-down menu.

• In the box Record Type, select Defect and then click OK.

• If you have an existing query, you can use it as a template in the Query wizard. Click Next if you don't want to utilize a template.

You can opt to Define how the query displays to choose which sections from the Defect record type to display in the results. You can select a field from the Fields pane by double-clicking or dragging it to the Display Format grid.

To see the required fields, go to the following link:

- Title - Owner - Priority - State –

The Sort Order column displays the query results in a predefined order. Fill in the Sort Order cells with numbers as follows:

-Priority 1 - State 2 - Owner 3 - Headline 4

After you've provided these details, sort the results using ascending or descending sort order.

The Select Fields to Use as Query Filters page defines which specific fields will be utilized as filters in the query criterion selection. After that, click Next twice on the state field.

Specify the fields and the selection criteria:

• Select Define Filter from the drop-down menu.

• Select Contains from the Operators list, then the NOT checkbox.

• Select Values from the drop-down menu. Choose Closed and then Resolved in the Select Values windows. Click OK once you've picked all of these options. The query has been successfully defined to display all records that are either open or closed.

• Now press the Run button.

• When you click Run, the results will appear in the Result grids, where you can examine them.

• In the File menu, choose Save As. This will allow you to save the query as well as the expected results to a personal folder.

6. List the structure of a good report created in Access.

Ans: The following sections must be included in a good access report:

a) Report Header- This section displays at the top of the report and contains the report's title as well as other pertinent information.

b) Page Header- A uniform title displays at the top of every page of the report to show that the page belongs to a specific report.

c) Group Header- Only when the report has the sort order and grouping levels does the group header show. On the basis of a data source field, the sort order and grouping headers are determined. Each report group has a group header at the top. Each group level has its own set of group headers.

d) Details- It's also known as the report's primary body. It contains information from tables or queries that serve as the report's record source. This part contains the report's core information content and is thus regarded as the most important.

e) Group footer- The group footer appears at the bottom of each level of grouping and contains summaries or subtotals for the grouped data.

f) Page Footer- This section comes at the bottom of each report page and displays page numbers, as well as the date and time the report was generated.

g) Report Footer- This section comes on the last page of the report and contains summaries or totals for all of the report's data.

7. List the ways to refine the design of a report.

Ans: Methods for improving the report's design include: - 

• Conditional Formatting: This is the most popular approach to format the report. By deleting duplicates, highlighting the proper information, and so on, this aids in retrieving the necessary amount of data.

• Data Grouping: This allows you to group together a particular amount of data that has the same or related information. Grouping ensures that data is collected and integrated from multiple unnamed cells into a single cell, allowing the user to quickly gather all of the information needed.

• Sorting Data: Sorting data organizes data in an ascending or descending order, alphabetically, or according to other sorting criteria. This also makes it easier for the consumer to find what they're looking for.

• Adding Dates and Page Numbers: A report's page footer contains two unbound controls in design mode: "current date" and "current page number of a total number of pages." The ‘Now ( )' function can be used to get the current date from the RTC. Similarly, the date and time can be entered by opening the Date and Time dialogue box by clicking ‘Insert percent date and time' from the menu bar.

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

Ans: Sorting is the process of systematically collecting data and giving your report a meaningful meaning by putting information in a sequence and sorting it according to particular criteria. This option can be used to categorize or group objects with similar attributes or characteristics, making it easier for the user to discover what they need.

9. What do you understand by saving a report as a snapshot?

Ans: A snapshot report is a report that contains the layout data and query results that were retrieved at a specific point in time. Snapshot reports are taken regularly and then saved to the report's server. The users cannot modify the detail and contents of the reports in a snapshot file.

10. State the procedure for creating a ledger in MS Access.

Ans: A set of processed data records containing information about each account's Code (Account Number), Account Names, Date, Particulars, Debit, and Credit Balance is necessary to create a ledger.

To collect this information, you'll need to write some SQL statements that will be saved as a Query. Let's pretend we stored the previous query as "Query P." The stages in the design view method in MS Access can be used to construct a ledger.

• First, in the Database window, select "Reports" from the "Object" list.

• After that, click "New" to open the "New Report" window.

• Then, from the combo control, select "Design View" and "Query P" and click OK.

• When you click OK, the access will display a blank report design separated horizontally into three sections: Page Header, Detail, and Page Footer. A list of Query P's possible fields is also provided for embedding on this blank design.

• Then, under the report's "Properties" column, pick the "Data" tab to define the recorded source as Query P. This will display a list of Query P's accessible fields.

• Right-click any element of the report design and select Report, Page Header, and Footer from the drop-down menu. When you do this, Access will respond by giving you two more parts, Page Footer and Page Header.

• Next, select a label control from the toolbar's "icon" and set it in the Page Header area. Make a caption for it.

• Now, while holding down the "Ctrl" key, click on each field to select all of Query P's fields. Then, in the "Details Section," drag and drop the appropriate field.

• Now, while holding down the "Shift key," click on all six fields to select total control. Next, right-click on the "Select Label Control" and select "Cut." Place the cursor in the Page Header Section to "paste" these controls.

• After that, select the "Properties" option supplied by MS Access.

• In the "Details Section," the text controls must now be aligned.

• Last but not least, the "Text Controls" and "Amount Field" must be selected and their properties updated.

As a result, the appropriate ledger has been established in MS Access.

11. Describe and discuss the procedure of creating the receipts side of a cash book.

Ans: The process of creating the receipts side of the cash book is similar to that of creating reports.

It is necessary to retrieve a set of processed data records containing information on Code (Account Name), Name of Accounts, Particulars, Date Debit Balance, and Credit Balance with reference to each specific account where cash is debited in order to construct the receipts side of the Cash Book.

To get all of this data, you'll need to write a few SQL queries and save them as Query in the database.

The receipts side of a Cash Book can be established in Access by following the procedures listed below.

1. In the design view, click the ‘Create Report' button. The access answers by providing a blank report design divided into three portions of the available fields of the Query: Page Header, Detail, and Page Footer.

2. Next, we use the tools offered by Access to create our report, such as naming it, defining its size, colour, and so on.

3. Drag and drop all of the fields from the Query into the ‘Details Sections.' As a result, the Cash Book's receipts section is completed.

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

Ans: Accounting reports are the tangible representation of accounting data. They serve as a summary of all pertinent facts and information pertaining to a specific incident. Accounting reports present the information contained in such a way that readers may grasp it without ambiguity. These reports serve as the foundation for decision-making since they reduce the risks associated with uncertainty. Accounting reports include ledgers, trial balances, cash/bank books, financial statements, and so on. Relevance, Timeliness, Accuracy, Completeness, and Summarization are five essential criteria of an accounting report that make it helpful.

The accounting reports can be divided into the two categories below.

1. Programmed Reports - These are reports that contain information that users can employ in circumstances that they anticipate occurring in the near future.

a) Scheduled Reports - These are reports that are generated on a regular basis. These reports can be generated on a regular basis, such as daily, weekly, quarterly, monthly, or yearly. Scheduled Reports include things like trial balances, ledgers, and cash transaction statements, to name a few.

b) On-Demand Reports - These are reports that are created on-demand or in response to the occurrence of a specific event. On-Demand Reports include Customer Statements of Accounts, Inventory Re-order Reports, Stock Purchased Reports, and so on.

2. Casual Reports - These are reports that contain information that may be valuable but is only required on a case-by-case basis. The users are unaware of their requirements. These are also known as ad hoc reports, and they can be prepared at any time without the help of a professional.

Creating Accounting Reports: A Step-by-Step Guide

The following three steps are involved in creating accounting reports in Access.

The first step is to design the report.

A report must be designed in such a way that it meets particular reporting objectives. It should not be either too long to be difficult to read or too short to hide vital information that is anticipated to aid in decision-making.

Step 2: Determine whatever accounting information queries exist.

Many SQL statements are constructed in such a way that the results of the previous SQL statement are used to determine the results of subsequent SQL queries. It then refines its findings by incorporating new data from existing data sets.

Step 3: Using the Final SQL Record Set

The final SQL recordset is a collection of report-oriented data. It is based on the SQL statement before it.

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

Ans: A query is a statement that returns refined data based on the user's conditions and criteria. It can simultaneously retrieve the needed data from numerous data sources, as well as change or remove multiple records. It serves as a link between interconnected tables, forms, and other data.

Accounting data for accounting reports are created by writing and running various queries in a database management system (DBMS). A query informs Access about the type of data we need to extract from one or more interconnected tables.

The following are the fundamentals of querying a set of interconnected data tables in MS-Access.

There is no or minimal data redundancy in relational DBMS since data is stored in multiple tables. However, the only way to get a complete picture of data contained across multiple tables is to run SQL queries. A query displays records including fields from many data tables. A SQL statement contains several parameters, including data definition, query, update, and the ability to construct user-oriented database views, establish security and authorization, and set integrity requirements.

Example:

The procedure of creating queries for inter-related data tables using SQL statements is explained below with the help of an example.

Select Code, Name, Sum (Amount)

From Vouchers INNER JOIN Accounts.

On (Accounts, Code = Voucher Debit)

Group By Code, Name.

14. Briefly explain the set of SQL statements to produce the receipts side of a cashbook for Model-I.

Ans: The SQL statements that are needed to generate the receipts side of a Cash Book for Model-I are listed below.

Step 1: To figure out how much the Cash Account is owed, multiply the total amount owed by the total

The Select clause is used to determine the total amount by which the Cash Account has been debited in all accounts. There are two fields in this clause.

(a) A code to identify the account (b) A code to calculate the amount that has been debited from the account

Statement in SQL

From Vouchers, Select Credit As Code, Amount, and Date

Where “231*” Is a Debit

Query 01. is the name given to this SQL statement.

The code and amount of money received are listed on this statement.

Step 2: SQL Statement to Generate the Total of Receipts

Group By Code Select Code Sum (Amount) AS Total FROM Query 01

Query 02 is the name given to this SQL statement.

Step 3: SQL Statement to Create a RecordSet with Account Code, Account Name, Credit Amount, and Date.

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

From Query 02 As an Accounts As b

Where a code=b code

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

Ans: The steps below are used to generate a query in MS Access using the design method:

• In the database window, select Queries from the object lists in the LHS. Double-click the “Create Query by Using Design View” button on the RHS.

• Access the responses by enabling a "Select Query" and the "Show Tables" window.

• From the menu bar, choose a view option (percent Total and percent Table Names).

• In the first column of the QBE grid layout, click the row labelled "field" to select the fields that will be used in the query.

• The table or query name will now be displayed in line with the field choices.

• Using Group and Aggregate, select the grid rows to define the clause to provide a summary to the query that will be created.

• To define the sort order on the fields, select any specific row of the grid.

• Check that the query result is presented correctly by clicking on the row.

16. Discuss the SQL view method of creating a query?

Ans: The Select Query panel can be used to determine the SQL view methodology for creating a query. Simply right-click on the table percent SQL view with your mouse. A pane replaces the upper and lower panes of the selected query, allowing you to specify the SQL statement that is programmed using the keyboard. After that, the SQL statement is directly placed into this pane and saved in a fashion that describes the design technique. The clauses listed below are typically used to build queries when writing SQL statements:

- Select - From - Inner - Left - Right - Where - Order By - Group By

1. Select: This clause determines which fields will be displayed when data or information is displayed. SELECT Code, Name, and Amount, for example.

The fields Code, Name, and Amount will be presented by the query statement, according to this statement.

2. From: This clause identifies the data source, which can be tables, queries, or a combination of the two. Two tables are combined when the JOIN clause is used. There are three different sorts of join clauses.

A) Inner - This Join clause only shows records that are identical in two different data sources. For Example, Inner Join Accounttype On (CatId = Type) From Accounts

B) Left - This Join clause displays all of the records in the primary table in a relationship, regardless of whether or not there are any matching records in the linked table. For Example, Join Account type On (Catid = Type) From Accounts Left

All of the Accounts records, as well as the matched entries, are included in the query data source.

C) Right - This JOIN clause displays all related table records in a relationship, regardless of whether or not there are any matching records in the primary table.

For example, Right Join Account type On (Catid = Type) From Accounts

3. Where: This clause specifies a criterion that limits the number of records returned by the query. The criteria supplied following the WHERE clause must be met by the query's result records.

4. Order By: This phrase determines the order in which the query's resultant records must appear. The list of fields supplied after the ORDER BY clause determines the basis on which this ordering is done.

5. Group By: This clause allows you to group records in order to create a summary query. The fields supplied after the GROUP BY clause serves as the foundation for grouping the summary results.

17. Describe the ways to refine the design of a report.

Ans: The following are some methods for locating a report's design:

i. Dates and Page Count: When a previously prepared report is run in design mode, the footer of the page of reports has two unbound controls: the current date and the current page count out of the total. Both of these controls can be adjusted to meet a specific demand.

ii. Deleting or Adding Report Manuals: Report controls can be deleted or added. After the report has been designed, the procedure can add or modify some report contents, based on its practicality. The toolbar provides the controls that allow all of these instructions to be executed.

iii. Conditional Formatting: In the same way as it replicates to the LO forms, this option may be used to enhance text boxes or combo boxes in reports. This function allows a user to apply certain unique text formats based on the value of a field.

iv. Sorting and Grouping: The purpose of grouping is to organize the information contained on the report into specific categories in an organized manner. The aggregated information is arranged in alphabetical, ascending, descending, or numerical order by sorting the content.

18. Explain the database design for Model-I for producing the receipts the series of SQL statements for producing the payment side of the cashbook for Model-II.

Ans: Follow these procedures to create the receipts side of the Cashbookina :

• Select Debit As Code from the drop-down menu. Total From Vouchers As Sum (Amount) Group By Debit; 

This enables you to determine the total amount debited from each account.

• Creating a consolidated set of accounts for debit totals

Select *

From Query I Union 

Select 

From Query 2

Select Code – Generating the net amount debited from the accounts 

From Query 3 

Group By Code, Sum (Total) As Net

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

Ans: For more information about the voucher, go here.

From the voucher main, select code, sum (amount) as the total amount from voucher main.

(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 the 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

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

Ans: 

1. To ascertain the total amount by which the accounts have been debited

SQL Statement

Select Debit As Code, Sum (Amount), As Total

From Vouchers

Group By Debit;

This SQL statement is saved as Query 01.

The GROUP BY clause retrieves the rows of vouchers table accounts-wise. The total of the debit amount in this query is given by the Total field with positive amounts.

2. To ascertain the total amount by which the accounts have been credited

SQL Statement

Select Credit As Code, Sum (Amount)* (–1) As Total

From Vouchers

Group By Credit;

This SQL statement is saved as Query 02.

We can see that the total of the credit amount has been multiplied by (–) 1. This is to ensure that the total credit amount should be in negative on contrary to the positive total of debit.

3. To generate a collective record set of accounts with their debit and credit totals

This collective recordset is generated by executing a union query between the above queries viz. Query 01 and Query 02.

SQL Statement

Select*

From Query 01

Union Select*

From Query 02

This SQL statement is saved as Query 03.

4. To ascertain the net amount with which an account has been debited or credited

SQL Statement

Select Code, Sum (total), As Net

From Query 03

Group By Code

This SQL statement is saved as Query 04.

A positive net amount implies a debit and a negative amount means a credit balance corresponding to an account code. This query can be used for generating a record set for Trial Balance.

5. To generate the recordset which consists of account Code, name of the account, debit amount and credit amount

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, Account As b

Where a.code=b.code;

What is a Database Management System (DBMS)?

A database management system is a framework programming for making and overseeing data sets. A DBMS makes it workable for end clients to make, peruse, refresh, and erase information in an information base. The most pervasive sort of information the board stage, the DBMS fills in as an interface among information bases and end clients or application programs, guaranteeing that information is reliably composed and remains effectively open.

What are the Functions of Database Management Systems?

The functions of a database management system aim to guarantee data consistency and integrity in the database. The following are the key functions of a database management system.

• Data dictionary management.

• Data Transformation and Presentation.

• Multi-User Access Control.

• Data Integrity Management.

• Database Communication Interfaces.

• Data Storage Management.

• Security and transaction Management.

• Backup and Recovery Management.

• Database Access Languages and Application Programming Interface.

Are you a Class 11 Accountancy student and looking for more information regarding Database Management Systems? If yes, then download Class 11 Accountancy Chapter 15 solutions pdf with a single click on the pdf link given below and go through Class 11 Accounts Chapter 15 solutions.

Objectives of CBSE Class 11 Accountancy Chapter 15

CBSE Class 11 Accountancy Chapter 15 deals with database management systems and helps the student identify the resources of MS ACCESS as a Database Management System. The  chapter helps the student create data tables, voucher forms, and queries. CBSE Class 11 Accountancy Chapter 15 enhances the student’s MS-Access skills.

Looking for revision and short-key notes for Class 11 Accounts Chapter 15 solutions? Download Vedantu’s NCERT Accountancy book Class 11 solutions pdf   given on this page to get access to complete revision and short-key notes. This material designed by Vedantu’s accounting experts consists of great practical examples also that help you grasp the concept quickly.

Solved Example:

1. ____________________ enables the business owner to record the introduction capital.

• Payments

• Expenditure

• Receipts

• Incomes

Ans: Receipts.

CBSE Class 11 Accountancy Chapter 15 is one of the most important topics in Class 11 Accountancy.

Check out Vedantu’s material of Accountancy Class 11 Chapter 15 solutions to enhance your knowledge of accounting and database management systems. Vedantu provides all the solutions to students that are struggling with the concept of data management system.

Fun Fact

• The statement ‘Accounting needs High-Level Math’ is a myth. Accountants use simple equations and basic arithmetic.