Detailed, Step-by-Step NCERT Solutions for 11 Accountancy Chapter 15 Accounting System Using Database Management System Questions and Answers were solved by Expert Teachers as per NCERT (CBSE) Book guidelines covering each topic in chapter to ensure complete preparation.
Accounting System Using Database Management System NCERT Solutions for Class 11 Accountancy Chapter 15
Accounting System Using Database Management System Questions and Answers Class 11 Accountancy Chapter 15
Test Your Understanding Fill in the blanks :
(a) Reports, the need for which is not anticipated is called …………………. reports.
(b) ………………… query does not involve use of any query function to produce a summary of data.
(c) ………………… query prompts the user to enter criteria for selecting a set of records.
(d) ………………… clause is used to specify the fields to display data or information.
(e) ………………… is meant to include page number, data and time of report.
(f) The purpose of is to organise the information of ………………… report into categories whereas arranges
information into numerical or alphabetical order.
(g) When saved as ………………… the contents of reports cannot be modified by the user.
Answer:
(a) Casual
(b) Simple
(c) Parameter
(d) SELECT
(e) Design view
(f) Sorting, sorting order
(g) Snapshot
Short Answer Type Questions
Question 1.
State what do you understand by accounting reports?
Answer:
A report displays information that is acquired from data processing and transformation in an organised manner. Reports tend to reduce the level of uncertainly associated with decision-maker and also influence their positive actions. The output of the computerised accounting system are accounting reports. An accounting system without reporting capability is incomplete as reporting is one of the main purpose for which an accounting system is designed and operated upon.
Data from various sources is collected and manipulated in such a way as to provide certain information. Then the related information is comprised to render certain use and such summarised information is called a report. The level and extent of the report varies according to the level it is submitted and the type of decisions to be based on it. In order to be effective a report must be accurate, timely and relevant.
In fact a report must have the following characteristics :
(a) Relevance
(b) Accuracy
(c) Timeliness
(d) Conciseness
(e) Completeness
A report is always prepared with a definite objective. Reporting has two objectives :
(1) To reduce the level of uncertainty that is faced by a decision-maker.
(2) To influence the behaviour of decision-maker.
Accounting report is the physical form of accounting information.
Reports, which do not meet the above states objectives, lack or do not have sufficient information context, have no value.
There are two types of accounting report:
(1) Program med Reports
(2) Casual Reports
(1) Programmed Reports : It contain informations useful for decision-making situations that the user have anticipated to occur. Programmed Reports are of two types :
(a) Scheduled Reports
(b) On Demand Reports
(a) Scheduled Reports – Reports which one produce according to a given time-frame, are called scheduled report. The time – frame may be daily, weekly, monthly, quarterly or yearly. Trial Balance, Ledger, Statement of Cash Transactions, Bank Book, Inventory Report, Profit & Loss Account and Balance Sheet, etc. are the examples of scheduled reports.
(b) On Demand Reports – Reports which are produced only on the triggering of some event, are called On Demand Reports. Customer’s Statement of Account, Inventory Records Report, etc. are the examples of on demand reports.
(2) Casual Reports : There are reports, the need for which is not anticipated, the information context of which may be useful but casually required. These are ad hoc reports and are generated casually by executingsome simple queries without requiring much of professional assistance. As opposed to programmed reports, casual reports are generated as and when required.
Question 2.
What do you mean by programmed or casual reports?
Answer:
An accounting system without reporting capability is incomplete as reporting is one of the main purposes for which an accounting system is designed and operated. The output of accounting system takes the form of accounting reports. A report is prepared with a definite objective. Every report is collection of related information for a particular need and purpose.
Accounting information generated by processing accounting data is gathered to generate an accounting report. An accounting report, in order to be useful, must display information context in such a manner as to give confidence to the user, influence his behaviour and prompt him to take positive action.
There are two types of accounting reports :
(1) Programmed Reports
(2) Casual Reports
(1) Programmed Reports: These reports contain information useful for decision-making situations that the users have anticipated to occur. There are two types of Programmed Reports :
(a) Scheduled Reports
(b) On Demand Reports
(a) Scheduled Reports – Reports which are produced according toa given time-frame, are called scheduled reports. The time¬frame may be daily, weekly,’monthly, quarterly or yearly. Cash Book, Bank Book, Inventory Report, Trial Balance, Profit & Loss A/c, Balance Sheet, etc. are the examples of scheduled reports.
(b) On Demand Reports- The reports, which are produced only on the triggering of some event, are called On Demand Reports. Inventory Re-order Statement, Creditor Statement, etc. are the examples of on demand reports.
(2) Casual Reports : These reports are casually required. There are reports, the need for wh ich is not anticipated, the information context of which may be Useful but casually required. These mzadhoc 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.
Question 3.
With the help of an example, briefly state the meaning of parameter queries.
Answer:
One of the most useful features of the modem database applications is the queuy, which provides us a way to question our database. The result of a query can then be printed or viewed on screen.
A query is a statement that communicates to Access the kind of information we need to entract from one or more table. Queries can be used as a source of information for forms and reports. Access executes the query each time we open the form or the report.
Parameter Queries is one of important type ofquerythat is required for generating reports.
Parameter Queries – A parameter query prompts the user to enter parameters, or criteria through an input box, For selecting a set of records. It is useful when there is a need to report the same query with different criteria. The criteria, this means, is not constant as in the case of the simple query. While entracting the transactions to prepare ledger accounts, the same set of queries need to he executed for different account code.
Taking example of following SQL Statement:
PARAMETERS Account Name Text (255)
SELECT Name
FROM Accounts
WHERE Code = Account No.
Here, the PARAMETERS clause is meant to declare the variable Account No. This SQL statement, when executed, prompts the user to provide the value of Account No.
Question 4.
Briefly state the purpose of functions in SQL environment.
Answer:
A function in the SQL environment is named and followed by parenthesis (). The function receives some inputs as its arguments and returns a value. These functions also form a part of the expression for a command field. The purpose of functions in SQL environment is to set the Control Source property of calculated controls and for to
form part of calculated field express in SQL statement.
There are three types of functions that are used in SQL statements
(1) Domain Aggregate Functions
(2) SQL Aggregate Functions
(3) Other Functions
(1) Domain Aggregate Functions – These functions are used to perform calculation based on values in a field of a table or query. All the domain aggregate functions use^he same syntax. DFunction (“Fid Name ‘, “Tbl Name” or “Qry Name”, “Srch Cond”)
Where
DFunction = Domain Aggregate Function Name Fid Name = Name of the field to searched Tbl Name = Name of table which contain the above field Qry Name = Name of query which contain the above field 1 Srch Cond = Search Condition on the basis of which the relevant record is searched.
(2) SQL Aggregate Functions – These functions are used in SQL Statements that provide the underlying record source of forms and reports. All these functions, when used require the GROUP BY clause in SQL Statement.
(3) Other functions –
(a) I IF: The purpose of this function is to Provide a value to the field from a mutually exclusive set of values.
(b) Abs: The purpose of this function is to return absolute value. This function receives a numeric value as its input argument and returns an absolute value.
(c) Val : The purpose of this function is to return the number contained in a string as a’ numeric value of appropriate type.
Question 5.
Briefly explains in steps the method of creating a query, using Wizard.
Answer:
Creating Query by using Wizard – The following steps are required in order to create a query using Wizard :
(i) Select queries from objects list given in left hand side of Database Window.
(ii) Double click at Create Query by using Wizard given on the RHS. Immediately, there is a window titled ‘Simple Query Wizard’ that prompts the user to select a field from a table or an existing query that is to be included in the query being created. Many such fields may be selected according to the information – requirement of the query. The table being chosen represent the data source of the query being created. The field being selected imply the data items to be displayed by the query. Use arrow buttons or double click at the list of fields on LHS of this window to select fields.
(iii) Click at Next after the desired fields have been selected. If the selected fields include number or query field, the designer is prompted to choose an option button to specify where the query to be created in a summary or in detail.
- If detail option is chosen, the execution of query results in displaying records from data source.
- if summary option is selected, the user is prompted to indicate the type of summarisation required –
Sum, Average, Minimum and Maximum with respect to the field of summarisation. Clicking at check boxes against different types of summarisation specifies this click OK.
(iv) Click at Next and specify the name of query being created % finish to save and execute the query. The result of the query are displayed in datasheet view.
Or
Create Query by using Wizard
(i) Start Access and open the database.
(ii) In the Database window Double Click on the Create Query by using Wizard.
The Simple Query Window will appear on the screen.
(iii) Select the table in the Table/Queries list box. Select the fields from the list displayed in Selected fields as shown in above figure.
(iv) Click the Next button. The Simple Query Wizard appears on the screen.
(v) Click the Detail or Summary button. click Next select Query window appears.
(vi ) Click at Next and specify the name of the query being created % Finish to save and execute the query.
Question 6.
List the structure of a good report created in access.
Answer:
Structure of a good report in access – The structure of a good report created in access contain seven sections. It is not necessary that every report created in access contain all the sections.
The seven sections of structure of reports are following :
(1) Report Header
(2) Page Header
(3) Group Header
(4) Details
(5) Group Footer
(6) Page Footer
(7) Report Footer
Report Header : It appears at the top of the report and includes title and other relevant information about the report.
Page Header : Page Header appears at the top of every page of the report. It may include a uniform title to indicate that page belongs to particular report.
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 it is the properties of this 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.
Details : It is also called the main body of a report. It contains data from tables or queries that provide the record source to a report. It consists of the main information context of a report so this section is the most important.
Group Footer : The Group Footer appears at the bottom of each grouping level and may contain summaries or sub-totals for the grouped data.
Page Footer : 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.
Report Footer : Report Footer appears once on the last page of the report to include summaries or totals for all data of this report.
Question 7.
List the ways to refine the design of a report.
Answer:
The design of the report can be created by any of the following methods :
- Auto Report
- By using Wizard
- By Design view
It may be improved or refined by making the following additions and modification to the report. For this purpose, an existing report is opening in Design mode.
(1) Adding Dates and Page Numbers
(2) Adding and Deleting Report Controls
(3) Conditionally Formatting Report Controls
(4) Grouping Levels and Sorting Order.
Adding Dates and Page Numbers: When report opened in design mode, the page footer contain the current date and current page number of total number of pages.
The date control use = Now ( ) function to retrieve the current date from RTC of computer. Format property of this control can modify the format of date by selecting General Date, Medium Date, Short Date or Long Date.
If a report is created using design view method, the date and/or time and page number may be added to any of its part.
The page number is added by clicking Insert % Page numbers from the menu bar to open the Page number dialog box, which allow the user to specify the format, position and alignment. The two formats are following:
(1) Page N (example Page 1)
(2) Page N of M (example Page 1 of 10)
The position to specify is either top of page or Bottom of page. Possible alignment are at centre, left, right, inside and outside.
‘Adding and deleting Report Controls : After the designing report, additional report controls may be added or deleted. Clicking tool bar icon opens report design tool bar, which contains a set of useful controls.
(i) Open report in design mode.
(ii) Click Field List button on report design tool bar.
(iii) Drag the field into an appropriate section of the report. The field appears with both label and text box control.
(iv) A field control may be deleted by selecting the control and Dressing the Delete key.
Conditionally Formatting Report Controls : The Conditional Formatting allows the designer to apply special text format that depends 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 amount exceeding certain limit or unexpected balances in some accounts.
Following steps are required to create a conditional formatting :
(i) Open report in design mode.
(ii) Select a control and click at Format on menu bar.
(iii) Click at Conditional Formatting.
(iv) Provide necessary conditions.
(v) The conditional formatting is deleted by re-opening the same dialog box and clicking at Delete button.
Grouping Levels and Sorting Order :
Grouping Level – The purpose of grouping is to organise the information content of a report into categories.
Sorting Order – It is meant to arrange such information content into numerical or alphabetical order.
When grouping and sorting of information, applied together, makes the report more meaningful and therefore useful to the user of the report.
The following steps are taken to specify the grouping and sorting order :
(i) Click at Sorting and Grouping icon of Report Design Tool bar. Access displays the following sorting and grouping dialog box.
(ii) Type field of accounts has been chosen on the basis of grouping the information content of trial balance. The group header and footer properly is set to yes to indicate that there is separate header and footer for each group of accounts in trial balance.
Question 8.
Briefly explain the purpose of grouping and sorting of the data as a means to define a report.
Answer:
The design of the report may be improved or refined by making the additions and modification to the report. This can be done by grouping and sorting of the data.
The purpose of grouping is to organise the information content of a report into categories. Sorting order is meant to arrange such information content into numerical or alphabetical order. With grouping the sorting applies to each individual group. The grouping and sorting of information, when applied together, makes report more meaningful and therefore useful to the user.
In order to specify the grouping and sorting order, following steps taken –
(1) Click at Sorting and Grouping icon of Report Design Tool bar. Access respond by displaying the following Sorting and Grouping dialog box :
(2) The LHS of this box provides a list of fields or expressions that are to be used for grouping and sorting. In the above dialog box, Type field Account has been chosen as the basis of grouping the information content of trial balance. The group header and footer properly is set to yes to indicate that there is separate header and footer for each group of accounts in trial balance.
Question 9.
What do you understand by saving a report as snapshot?
Answer:
After the creation of report, it may be saved in such a manner so as to be viewed by other without the help of Access. It is possible by saving the report as a snapshot file. As a result with help of Adobe.
Acrobat software, high quality picture image of each page of report is created. Other users of the report can then view the report and print any of its pages without being able to modify its contents. In order to create a report snapshot, following steps are required –
(1) Select and generate a report in Database Window.
(2) Click File % Export from menu bar. An Export Report dialog box appears.
(3) Choose the folder from combo box next to Save in, provide a file name; select snapshot from list control next to Save as type and click at Save button. While saving the report ensure that the auto start check box is enabled.
The generated report is saved as a snapshot and can be supplied to others for printing and viewing without the help of Access database.
Question 10.
State the procedure for creating ledger in MS Access.
Answer:
Financial Accounting Reports such as Cash Book, Bank Book, Ledger Account, Trial Balance etc. may be generated in Access by adhering to report generation process.
The ledger is one of accounting report, which show the transaction ( of a particular account, during a given period of time. The format of typical ledger is as given :
data records each of which provide information Code (Account Number), Name of Account, Particulars, Date, Debit Balance and Credit Balance with reference to each particular amount.
To get all the above information we have to make certain SQL statements and save them as query. Lets save the last query as Query L.
Now in access we can create the Ledger Account by taking following steps :
The Ledger is generated using the Design view method.
1. Select Reports from the object list in Database window.
2. Click at New button. This displays the New Report Window.
3. Select Design view and Query L from combo control.
4. Click OK.
Access responds by displaying a blank report design divided horizontally into three sections – Page Header, Detail and Page Footer. Besides, a list of available fields of Query L is also provided for embedding on to this blank design of report.
5. Click at properties of report and select data tab to define the record source as Query L. This gives a list of available fields of Query L.
6. Right click at any part of report design and choose report Page Header and Footer. Access responds by providing two more sections – Page Header and Page Footer.
7. Click at the icon of toolbar and pick up a label control to be placed at Page Header Section and assign set its caption properly to (account name), Font Size to 16, Font colour to Blue, Text align to Left and Font weight to Bold.
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 Details section.
9. Select the lable control of all the six fields by clicking at each while keeping the shift key pressed. Right click at select label control and choose Cut. Place the mouse at Page Header Section and Paste these control.
10. Choose the properties provided by Access.
11. Align the text controls in Detail section.
12. Select the text controls and amount field and modify their properties.
This way we can create the ledger in Access.
Long Answer Type Questions
Question 1.
Describe and discuss the procedure of creating the receipts side of a cash book.
Answer:
Cash book is a type of accounting report. The procedure of creating the receipts side of cash book is as same as report generation. To produce receipts side of a cash book, it is necessary to retrieve a set of processed data records which provide information on code (Account Number), Name of account, Particular and Credit Balance with reference of each account where cash account is debited. We have to find out the amount and particulars of the transaction in which cash is received i.e. cash account is debited. Firstly, to find out these amounts and particular we have to make a set of SQL statements and save them into Database after giving particular name to each query.
After this we have to start the MS Access database-
Select there create report in design view and Query (last saved). This respond to give blank report design divided into three sections – Page Header, Detail and Page Footer and of available fields of Query. Here, we design our report with the tools provided by Access like giving name to the report, defining its size and colour. After this we select all the fields from our Query and drag and drop them into the Details section. By applying some arithmetical calculations provided by Access we get the receipt side of cash book.
Question 2.
Discuss the concept of accounting reports. Explain the three steps involved in creating such reports.
Answer:
A report displays information that is acquired from data processing and transformation in an organised manner. Reports tend to reduce the level of uncertainly, associated with decision-maker and also influence their positive action. The output of the Computerised Accounting System are accounting reports. An accounting system with-out reporting capability is incomplete as reporting is one of the main purpose for which an accounting system is designed and operated upon. Cash Book, Bank Book, Ledger, Trial Balance, Profit & Loss Account and Balance Sheet are the examples of accounting reports.
Data from various sources is collected and manipulated in such a way as to provide certain information. Then the related information is comprised to render certain use, and such summarised information is called accounting report. The level and extent of the report varies according to the level it’s submitted and the type of decision to be based on it. In order to be effective a report must be accurate, timely and relevant.
An accounting report is always prepared with a definite objectives. Every report is a collection of related information for a particular need and purpose. It must satisfy following two objectives –
(1) To reduce the level of uncertainty that is faced by a decision-maker.
(2) To influence the behaviour of decision-maker. .
Accounting informtion generated by processing of accounting data
is gathered to generate the accounting report. An accounting report therefore, is the physical form of accounting information. Useful accounting reports must have five following characteristics –
(1) Relevance
(2) Timeliness
(3) Accuracy
(4) Completeness
(5) Summarisation
(1) Relevance: To be useful, report must be relevant to the decision making needs of the user. Report has the quality of relevance when it influences to economic decision of the user by helping them to evaluate past, present or future events or confirming or correcting their past evaluation. The productive and confirmatory role of report are inter-related.
(2) Timeliness : If there is undue delay in reporting, it may lose its relevance. To.provide report on a timely basis, it may often be necessary to report before all aspects of a transaction or other events are known. Conversely, reporting is delayed until all aspects are known, the report may be highly reliable but of little use to the user who have had to make decision in the interim period.
(3) Accuracy : Accounting report be accurate and present true and fix view of the financial position and performance of the enterprise.
(4) Completeness : To be reliable report in accounting must be complete within the bounds of materiality and cost. An omission can cause report to be false or misleading and thus unreliable and deficient in terms of decision-making.
(5) Summarisation: Report should be summarised in manner which makes it attractive and useful to users.
An accounting report, in order to be useful, must display information content in such a way 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.
Types of Accounting Report :
(1) Programmed Reports
(2) Casual Reports
(1) Programmed Reports : It contain information useful for decision-making situations that the user have anticipated to occur. Programmed Reports are of two types :
(a) Scheduled Reports
(b) On-demand Reports
(a) Scheduled Reports – Reports which are produced according to a given time frame, are called scheduled report. The time¬frame may be daily, weekly, monthly, quarterly or yearly. Trial Balance, Ledger, Statement of Cash, Bank Book, Inventory Report, Profit & Loss Account and Balance Sheet, etc. are the examples of scheduled reports.
(b) On-demand Reports – Reports which are produced only on the triggering of some events, are called On-demand Reports. Customer’s Statement of Accounts, Inventory Re-order Report, etc. are the examples of On-demand Reports.
(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 ad hoc 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 process of creating accounting reports in Access involves following three steps:
(1) Designing the Report
(2) Identifying the Accounting Information Queries
(3) Creating an Accounting Report
(1) Designing the Report: Every report is expected to meet certain objectives of reporting for which it is designed and developed. It must has the five main characteristics of good report. It should not be too big so as not to be read at all or too small so as to missing some important information that is expected to facilitate the decision-making. Objective-oriented reporting means designing the report in such a manner as to meet the pre¬determined objectives in view.
(2) Identifying Accounting Information Queries : A number of SQL statements are written in such a way that each successive SQL statement relies upon the results of the preceding SQL statement and refines its results by using fresh data from existing data tables or queries.
(3) Using the Report Set of Final SQL Statements: The record set . of final SQL statement that relies upon the preceding SQL statement, is collection of report-oriented information. This record set need be embedded 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 filtered data aeeordingto one’s conditions and specifications. A query may be created to find and retrieve the exaet data that meets the specific conditions. A query can retrieve wanted data from multiple tables at one time.
A query can also update or delete multiple records at the same time and can perform predefined calculations on the given data. A query is a link between inter-related tables, forms etc.
Accounting information that is presented in an accounting report is generated by creating and executing various queries using DBMS. A query is a statement that communicates to Access the kind of information we need to extract from one or more inter-related tables. Queries can be used as a source of information for forms and reports.
The basics of creating queries in MS Access with a set of inter-related data tables are discussed below :
As we know that Relational Database Management System stores data in different table (Relations) so that there is no or minimum data redundancy. But for a complete view of data stored across various tables is achieved only by executing queries based on SQL. A query is capable of displaying records containing fields from across a number of data tables.
SQL has statement for data definition, query and update. Besides this, it has the capability to define user-oriented views of database, specify security and authorisation, define integrity constraints and various other operations. Various SQL statements are used to create queries for inter-related data tables. Consider this following statement:
SELECT Code, Name, Sum (Amount)
FROM Vouchers INNER JOIN Accounts
ON (Accounts, Code – Voucher. Debit)
GROUP BY Code, Name
In the above query, the vouchers table has been joined with Accounts table.on the basis of Code field of Accounts and Debit field of vouchers. The result record set has been grouped on the basis of Code and name of accounts. The sum of account for each group has been ascertained and displayed.
Consider another SQL statement
SELECT Debit As Code, Name, Category FROM Vouchers, Accounts, Account Type WHERE Debit – Code AND Type = Cat ID AND category = “Expenses”
In the above query, vouchers table, Accounts table and Account Type table are joined on the basis of Debit field, Code field and Cat ID field respectively to retrieve Code, Name and Category of Expense accounts which have been debited.
Question 4.
Briefly explain the set of SQL statements to produce the receipts side of a Cash Book for Model-I.
Answer:
The following series of SQL statement retrieve a record set of producing the
receipts side of cash book for Model – I:
(a) To find the total amount by which the Cash Account is debited.
In order to ascertain the total amount by which every transacted Cash Account has been debited, the
SELECT Clause need to have two fields – one.code to identify the account and another to generate the total by which such account has been debited.
SQL Statement:
SELECT Credit As Code, Amount, Date
FROM Vouchers
WHERE Debit Like “631 ”
This SQL statement gives the code and amount from which cash is received.
This SQL statement is saved as Query C2.
(b) To generate the total of Receipt side.
SQL Statement:
SELECT Code Sum (Amount) As Total
FROM Query Cl
GROUP BY Code .
This SQL statement is save as Query C2.
(c) To find the record set which consist of account code, name of account, credit amount and date.
SELECT a. code b. name As [Name of Account], IIF (a = Total > 0, a. Total, null As Amount)
FROM Query C2 As a Accounts As b WHERE a code = b code
Question 5.
Describe in steps the design view method to create a query in MS Access?
Answer:
The following steps are required to create a query in MS Access by using design view :
(1) Select Queries from object list of database window.
(2) Double Click at Create Query by Using Design view.,
This will display the Select Query and Show Table window. [Select Query Windows is divided into two panes :
(i) Upper pane
(ii) Lower pane
The upper pane is meant to display Table/Queries. The lower pane is also called Query By Example (QBE) grid, has one column each for field to be included in query being created. The row of this grid shows field name, table/query, sort order and also the criteria that have been applied to the field or fields to restrict the query results. The Show Table Window is meant to add tables, queries or both to the upper pane of Select Query Windows.
(3) Click at view of menu bar % Total and then % Table names.
(4) Click at field row of first column of QBE grid to select the fields to be included in the query.
(5) The name of table or query is displayed, according to selection of fields. Such tables/queries constitute the data source shown after FROM clause of SQL statement. However, the initial selection of a table/query in the second row of QBE grid restricts the choice of fields to the selected table/query only.
(6) Click at row of grid to specify the group by clause and aggregate functions so that summary a query is created.
(7) Click at row of grid to specify the sort order on fields.
(8) Click at row to check for the selected field to be displayed in the query results.
• Click at row of the grid to-specify the criteria to limit the records to be displayed by the query being created.
• Click at File % Save to save a query. A dialog box appears to name the query created.
Or
(1) Click at Queries tab under objects in Database Window.
(2) Click at New. This will display New Query dialog box.
(3) Select the Design view and click at OK. This will display Show Table.
(4) Select desired table/query. Click at Add button.
(5) Click at dose button to close show table. This will display Query Design Window.
(6) In order to add a field to the design grid from table/query in the field list section of the Query Design Window. One has to double click on the field he wishes to add.
Repeat this until all the required fields are added to the query.
(7) Save the query window using command Save As on File Menu.
(8) To specify the sort criteria, click at sort row below the desired field in the design grid of Query’ Design Window and then click on the down arrow and select Ascending or Descending.
(9) Select row of the grid to specify the criteria to limit the records to be displayed by the query being created.
Question 6.
Discuss the SQL view method of creating a query?
Answer:
A query may be created directly in Select Query Pane by a right click at table pane % SQL view.
(1) Specify the SQL statement by typing at keyboard.
(2) Click at OK.
It directly save SQL Statement, the following clauses are normally used for generating informations queries –
(I) SELECT : It specify the fields to display data or information. SQL Statement – SELECT Code, Name, Amount.
Code, Name, Amount fields are display by the query statement.
(II) FROM : Tt specify the source of data for answering the query. SQL Statement –
SELECT Name FROM Employees
This shows the name of employees from the Employees table.
(III) JOIN-: It join the two tables based’ on condition of join. There are three types of join –
(a) INNER JOIN
(b) LEFT JOIN
(c) RIGHT JOIN
(a) INNER JOIN – It display only exactly matching records between data sources.
SQL Statement –
FROM Accounts INNER JOIN Accounts Type ON (Cat ID = Type)
It display only those records from Accounts and Accounts Type table which match exactly Cat ID = Type.
(b) LEFT JOIN : It displays, all the records to the primary table in the relationship irrespective whether there are matching records in the related table or not.
SQL Statement
FROM Accounts LEFT JOIN Account Type ON (Cat ID = Type)
It displays all records of Accounts along with matching records of Account Type table which match condition Cat ID = Type.
(c) RIGHT JOIN : It displays, al l the records of related table in the relationship irrespective whether there are matching records in the Primary table or not.
SQL Statement —
FROM Accounts RIGHT JOIN Account Type ON (Cat ID = Type)
It displays all records of Account Type table along with matching records of Accounts table.
(IV) WHERE: It is used to provide the condition to restrict the records to be returned by query. The result of query must satisfy the condition which is specified after WHERE.clause-
(V) ORDER BY : It 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.
SQL Statement – ORDER BY Type, Code
In context of Account table, it implies that the resultant record sat is ordered by the Type field of Accounts and with in Type, by Code field of Accounts.
(VI) GROUP BY: It enables grouping of records for creating summary query. The fields after GROUP BY clause constitute the basis of grouping for which summary results are obtained.
SQL statement –
SELECT Debit Sum (Amount)
FROM Vouchers GROUP BY Debit
In this statement, the GROUP BY clause uses Debit Account Code as the basis for computing the sum of the amount of voucher.
Question 7.
Describe the ways to refine the design of a report.
Answer:
The design of the report can be created by any of the following methods :
• Auto Report
• By using Wizard
• By Design view
It may be improved or refined by making the following additions and modification to the report. For this purpose, an existing report is opened in Design mode.
(1) Adding Dates and Page Numbers
(2) Adding and Deleting Report Controls
(3) Conditionally Formatting Reports Control
(4) Grouping Levels and Sorting Order.
(1) Adding Dates and Page Numbers : When report opened in design mode, the page footer contain the current date and current page number of total number of pages.
The date control uses = Now ( ) function to retrieve the current date from RTC of computer. Format property of this control can modify the format of date by selecting General Date, Medium Date, Short Date or Long Date.
If a report is created using design view method, the date and/or time and page number may be added to any of its part.
The page number is added by clicking Insert % Page number from the menu bar to open the Page number dialog box, which allow the user to specify the format, position and alignment. The two formats are following:
(1) Page N (example Page 1)
(2) Page N of M (example Page 1 of 20)
The position to specify is either Top of page or Bottom of page. Possible alignment are at centre, left, right, inside and outside.
(2) Adding and Deleting Report Controls : After the designing report, additional report controls may be added or deleted. Clicking tool bar icon opens report design tool bar, which contains a set of useful controls.
(i) Open report in design mode.
(ii) Click Field List button on report design tool bar.
(iii) Drag the field into an appropriate section of the report. The field appears with both label and text box control.
(iv) A field control may be deleted by selecting the control and pressing the Delete key.
(3) Conditionally Formatting Report Controls : The Conditional Formatting allows the designer to apply special text format that depends 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 amount exceeding certain limit or unexpected balances in some accounts. Following steps are required to create a conditional formatting:
(i) Open report in design mode.
(ii) Select a control and click at Format on menu bar.
(iii) Click at Conditional Formatting.
(iv) Provide necessary conditions.
(v) The conditional formatting is deleted by re-opening the same dialog box and clicking at Delete button.
(4) Grouping Levels and Sorting Order :
Grouping Levels – The purpose of grouping is to organise the information content of a report into categories.
Sorting Order – It is meant to arrange such information content into numerical or alphabetical order.
When grouping and sorting of information, applied together, makes the report more meaningful and they are useful to the user of the report for decision-making purpose.
Question 8.
Explain the database design 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 payment side of cash book of Model-II :
(1) SQL Statement
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 “631 *”
(2) SQL Statement
SELECT Narration, Code, Amount FROM vouchers As v, Details As D WHERE tType = 0 AND V.VNO = D.VNO AND code Like “631*”
(3) SQL Statement
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 “631 *”
UNION
SELECT Narration, Code, Amount FROM vouchers As v, details As D WHERE tType = 0 AND V.VNO = D.VNO
ANDacc-code like“631*”
This SQL statement is source as Query D1
(4) SQL Statement
SELECT Code, Sum (Amount) As Total FROM Query Dl
GROUP BY code .
This SQL statement is saved as Query D2.
(5) SQL Statement
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 database design for Model-11 is used.
Answer:
The following series of SQL statements retrieve, the record set for producing trial balance when database design for Model-Il Is used. In addition to this, the accounts have categorised within the trial balance according to the Account Type – Expense Revenues, Assets and Liabilities.
(1) To find the total amount by w hich the accountsbave been debited.
The transacted amount have been stored Aee Code of Vouchers Main and code of Vouchers Detail. C
SQL Statement for Vouchers Details –
SELECT code, SUM (amount), As total FROM vouchers Main INNER JOIN voucher details ON Voucher Main. VNO = Vouchers Details. VNO WHERE Type = 0
GROUP By Code; .
SQL Statement for vouchers Main tabic
SELECT AccCode as Code, Sum (aniqtmjl); As total ‘v- . FROM vouchers Main INNER JOIN voucher Details Voucher Main. VNO “ vouchers Details
WHERE Type = 1
GROUP By AccCode;
Following SQL Statement merged both the above SQL Statements using UNION Clause –
SELECT Code, sum (amount), As total FROM vouchers Main INNER JOIN voucher Details ON Voucher 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 voucher Details ON Voucher Main. VNO = Vouchers Details. VNO WHERE Type = 1 GROUP BY AccCode;
This SQL Statement is saved on Query 101 for its subsequent use. The total of debit amount in this query represents the total with the positive amounts.
(2) To find the total amount by which the account have been credited.
In order to ascertain the total by which very transacted account has been credited the following SQL statements are required : SELECT Code, sum (amount)*-1, As total FROM vouchers Main INNER JOIN voucher Details ON . Voucher Main. VNO = Vouchers Details. VNO WHERE Type =1 GROUP BY Code, Amount UNION
SELECT AccCode As Code, sum (amount)*-!, As total FROM vouchers Main INNER JOIN voucher Details ON Voucher 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 amount of credit is always negative just as amount of debit is taken as positive.
This query is saved as Query 102.
(3) To find a collective record set of accounts with their debit and credit totals.
A collective record set is produced 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 to net amount.
SQL Statement – SELECT*
FROM Query 101
UNION
SELECT*
FROM Query 102
It causes horizontal merger of Query 101 and Query 102.
This SQL statement is saved as Query 103.
(4) To find the net amount with which an account has been debited or credited.
SQL Statement
SELECT Code, Sum (total), As Net FROM Query 103 GROUP BY Code;
This SQL statement is saved as Query 104.
(5) To find the record set which consists of Account Code, Name of Account, Debit Amount and Credit Amount.
This query provide 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, I1F (a. Net < 0, abs (a. Net), null) As Credit FROM Query 104 As a, Account As b .
WHERE a.code = b.code;
This SQL statement is saved as Query 105 Trial Balance with sorting and Grouping levels.
(6) To find the record set of accounts with their category and category ID.
SQL Statement
SELECT Account Code, Accounts Name, Category, Cat ID
FROM Accounts
INNER JOIN Account Type ON
Accounts. Type = Account type. Cat ID;
This SQL statement is saved as Query 106,
(7) To find the record set consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with category details.
SQL Statement
SELECT a.Code, b.Name As [Name of Account];
ILF (a.Net > 0, a.Net, pull) As Debit, IIF (a.Net < 0, abs (a.Net), (null) As Credit, Category, Cat ID
FROM Query 104 As a. Query s 06‘As b WHERE a.code = b.code; –
This SQL statement is saved as Query 107 to provide information details for designing trial balance with grouping and sorting of the accounts.
Question 10.
Using Model-1 discuss the series of SQL statements to produce a trial balance upto a particular date. ‘
Answer:
The fbl lowing series of SQL statements retrieve a record set for producing trial balance when database design for Model-I based.
(1) To find the total amount by which the accounts have bee ;
debited.
SQL Statement –
SELECT Debit As Code, Sum (amount). As total
FROM vouchers
GROUP BY Debit; GR0UPJ3Y clause retrieves the rows of vouchers tafile accounts-
wise because bitfield refers to account code. This SQL statement ‘ is saved as Query 01. The total of debit amount in this query is given by Total field with positive amounts.
(2) To find the toMhmount by which the accounts have been ; credited.
SQL Statement –
SELECT Credit As Code Sum (amount)* (- S) As total
FROM vouchers GROUP BY Credit;
The sum of amount generated by Sum (Amount) is multiplied by -1 so that the final amount assigned to total field is always negative. 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.
This SQL statement is saved as Query 02.
(3) To generate a collective record set of accounts with their V debit and credit totals.
This collective record set is generated by executing a union query
between Query 01 and Query 02. Q
SQL Statement –
SELECT*
FROM Query 01
UNION
SELECT
FROM Query 02
This SQL statement is saved as Query 03.
(4) To generate the net amount with which an account has been debited or credited.
SQL Statement
SELECT Code, Sum (total), As Net FROM Query 03
GROUP 6Y Code …
A positive net amount implies a debit and negative amount means a credit balance corresponding to an account code. This SQL statement is saved as Query 04 use in generating record set for trial balance.
(5) To find that record set which consists of Account Code,Name of Acopamhi Debit Amount and Credit Amount.
SQL Statement –
SELECT a.code b.namg-As [Name of account],
IIF (a.Net > 0,-a.Net) As Debit,
IIF (a.Net < 0, abs (a.Net), null) As Credit As Credit
Query 04 As a, Accounts ASb .
Where 8 %.cctde = b.code;
This SOL statement is saved as Query 05 for providing the necessary a formation content for Trial Balance Report.