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

Page No. 629

Question 5: Describe in steps the design view method to create a query in MS Access?
Answer: 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.
a) 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.
b) 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.

Question 6: Discuss the SQL view method of creating a query?
Answer : The following clauses are used for generating information queries using SQL
statements.
1. SELECT: This clause specifies the fields to display data or information. For example
SELECT Code, Name, Amount
This statement indicates that the fields Code, Name and Amount are to be displayed by
the query statement.
2. FROM: This clause specifies the source of data in terms of tables or queries or a combination of both. By specifying the JOIN clause, two tables are joined. Join clause
can be of following three types.
a) INNER- This Join clause displays only those records that are exactly matching between two data sources. For example, FROM Accounts INNER JOIN AccountType ON (CatId = Type)
Here, only those records of Accounts and AccountType table are a part of source of query data, which match exactly on CatId = Type.
b) LEFT- This JOIN clause displays all the records in the primary table in a relationship
irrespective of whether there are matching records in the related table or not. For
example, FROM Accounts LEFT JOIN AccountType ON (CatId = Type)
Here, all the records of Accounts along with the matching records form a part of the source of the query data.
c) RIGHT- This JOIN clause displays all the records of related table in a relationship irrespective of whether there are matching records in the primary table or not.
For example, FROM Accounts RIGHT JOIN AccountType ON (CatId = Type)
Here, all records of the AccountType along with the matching records of the Accounts
table form a part of the source of query data.
3. WHERE: This clause provides the condition to restrict the records to be returned by the query. The result records of the query must satisfy the condition which is specified after the WHERE clause.
4. ORDER BY- This clause specifies the order in which the resultant records of the
query are required to appear. The basis on which this ordering is done is determined by
the list of the fields specified after the ORDER BY clause. For example, ORDER BY Type, Code
This statement implies that the resultant record set is ordered by the 'Type' field of Accounts and within Type, by 'Code' field of Accounts.
5. GROUP BY- This clause enables the grouping of records for creating summary query. The fields specified after the GROUP BY clause form the basis of grouping for which the summary results are obtained. For example,
SELECT Debit, Sum (Amount)
FROM Vouchers
GROUP BY Debit
Here, the GROUP BY clause uses debit account codes for calculating the sum total of
the amount of the vouchers.

Question 7: Describe 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.
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.
a. Open the report in the design view and click 'Field List' button on report design tool bar.
b. Now, drag the field into an appropriate section of the report, so that the field appears with both label and text box control.
c. 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.
a. Open report in the design view and select a control and click at 'Format' on the menu bar.
b. Now, click at 'Conditional Formatting' and provide the necessary conditions for formatting.
c. 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.

Question 8: 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.
Answer : 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

Question 9: Describe the series of SQL statements to produce trial balance data base design for Model-II is used.
Answer : In the following set of SQL statement, the accounts have been categorised within the Trial Balance according to the Account Type as: Expenses, Revenues, Assets and Liabilities.
The below mentioned series of SQL Statement retrieve the record set for producing Trial Balance when database design for Model-II is used.
SQL Statements

1. To ascertain the total amount by which the accounts have been debited
The transacted accounts have been stored AccCode of vouchers main and code of
Vouchers Details.
SQL Statement for Vouchers Details
SELECT Code, SUM (amount), As total
FROM vouchers Main INNER JOIN voucher Details
ON Voucher Main. VNO=voucher Details. VNO
WHERE Type=0
GROUP BY Code;
SQL Statement for Vouchers Main Table
SELECT AccCode As Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=vouchers Details. VNO
WHERE Type=1
GROUP BY AccCode;
Now, we need to join the above mentioned SQL statements using UNION clause. This
is done by the SQL command.
SQL Statement for Merging Voucher Details and Vouchers Main Table
SELECT Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=0
GROUP BY Code;
UNION ALL
SELECT AccCode As Code, sum (amount), As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=1
GROUP BY AccCode;
This SQL Statement is saved on Query 01 for its subsequent use.
The total of debit amount in this query represents a positive total.

2. To ascertain the total amount by which the accounts have been credited
The following series of SQL statements help in ascertaining the total by which every
transacted account has been credited.
SELECT Code, sum (amount)* -1, As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=voucher Details. VNO
WHERE Type=1 GROUP BY Code, Amount
UNION
SELECT AccCode As Code, sum (amount)* -1, As total
FROM vouchers Main INNER JOIN voucher Details ON
Voucher Main. VNO=vouchers Details. VNO
WHERE Type=0 GROUP BY AccCode, Amount;
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.
This query is saved as Query 02.

3. To compute a collective record set of accounts with their debit and credit totals
In order to pool out a collective record set of accounts, a union query between the
saved Query 01 and Query 02 is passed. This is done to ensure that the debit and the
credit amount with respect to each account become available for generating the net
amount (Debit - Credit).
The required SQL Statement for Union query
SELECT*
FROM Query 01
UNION Select*
FROM Query 02
This statement results in a horizontal merger of the above queries, viz. Query 01 and
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
UNION Select*
GROUP BY Code;
This SQL statement is saved as Query 04.

5. To form the record set which consists of Account Code, Name of Account,
Debit Amount and Credit Amount
This query provides relevant information to the Trial Balance report.
SQL Statement
SELECT a. Code b. name As [Name of Account],
IIF (a. Net>0, 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;
This SQL statement is saved as Query 05
Trial Balance with sorting and Grouping levels

6. To ascertain the record set of accounts with their category and category ID
SQL Statement
SELECT Accounts.Code, Accounts Name, Category, CatId
FROM Accounts
INNER JOIN Account Type ON
Accounts. Type=Account type, CatId;
This SQL statement is saved as Query 06.

7. To ascertain the record set consisting of Account Code, Name of Account,
Debit Amount and Credit Amount along with their category details
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, Category,
CatId
FROM Query 04 As a, Query 06 As b
WHERE a.code=b.code;
This SQL statement is saved as Query 07. This query provides information details for
designing Trial Balance with grouping and sorting of the accounts.

Question 10: Using Model-III* discuss the series of SQL statements to produce a trial balance up to a particular date.
*Since, only Model-1 and II have been discussed in this chapter, accordingly we have attempted the question using Model-I only.
Answer: The below mentioned series of SQL Statement retrieve the record set for producing Trial Balance when database design for Model-I is used.
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 record set 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 negative amount means a credit balance corresponding to an account code. This query can be used for generating record set for Trial Balance.
5. To generate the record set which consists of account Code, name of 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;
This SQL statement is saved as Query 05. This query can be used for providing the necessary information content for the Trial Balance Report.
Important Note: There is a mistake in the Question No. 10 of the book. The correct question should be on Model- I instead of Model-III.