Kerala Plus One Accountancy Chapter Wise Questions and Answers Chapter 12 Structuring Database for Accounting
Plus One Accountancy Accounting System Using Database Management System One Mark Questions and Answers
Reports, the need for which is not anticipated is called_______reports.
_______query does not involve use of any query function to produce a summary of data.
_______query prompts the user to enter criteria for selecting a set of records.
______clause is used to specify the fields to display data or information.
______is meant to include page number, data and time of report.
The purpose of______is to organise the information of report into categories whereas______arranges information into numerical or alphabetical order.
Sorting, sorting order
When saved as_______,the contents of reports can not be modified by the user.
Plus One Accountancy Accounting System Using Database Management System Two Mark Questions and Answers
List out the ways in which quarries may be created in MS-access.
- Wizard method
- Design method
- SQL view method
List the structure of a good report created in Access.
A good report created in Access may be designed using seven sections which taken together constitutes the structure of report design. The structure of a good report should have the following sections in it.
- Report Header
- Page Header
- Group Header
- Group Footer
- Page Footer
- Report Footer
List the ways to refine the design of a report.
The design of the report created by any methods can be improved upon by making the following additions and modifications to the report.
- Adding Dates and Page Numbers
- Adding and Deleting Report Controls
- Conditionally Formatting Report Controls
- Grouping Levels and Sorting Order
What do you understand by saving a report as a snap-shot?
When a report is created it become important to save it in such a manner so that it can be viewed whenever the user feel like doing so. In this context through a snapshot a report may be saved in such a manner so as to be viewed by others without the help of Access. This becomes possible by saving the report as a snapshot file.
As a result, a high quality picture image of each page of report is created with Adobe Acrobat sofware. Other users of the report can then view the report and print any of its pages without being able to modify its contents.
Plus One Accountancy Accounting System Using Database Management System Three Mark Questions and Answers
State what do you understand by accounting reports?
An accounting report is basically the physical form of accounting information. Accounting reports are reports which display information that is acquired from data processing and transformed in an organised manner. Here it is worth mentioning that a report is prepared with a definite objective.
Every report is a collection of related information for a particular need and purpose and must meet the twin objectives of reporting; First to reduce the level of uncertainty that is faced by a decision-maker; second to influence the behaviour (or positive actions) of the decision-maker.
Reports can be classified into two broad categories.
Discuss the capabilities of MS Access
Capabilities of MS Access are as follows:
- Storing the data in an organised manner.
- Enforcing data integrity constraints.
- Representing complex relationship among data.
- Restricting unauthorised assess to database.
- Allowing fast retrieval of data with or without processing by using SQL.
- Flexibility to create multiple user interface.
What do you mean by Query in Access. Name the types of queries.
A query is a request for data or information from a database table. This data may be generated as results returned by structured query language (SQL) or as pictorials, graphic or complex results.
A query is used to extract data from a database in a readable format according to the users request.
Types of queries:
There are several types of queries in access that are used to generate information. Such queries are called “select queries” because they are used to “select” records with a given set of fileds. There are three types of queries.
- Simple query
- Parameter queries
- Summary queries
With the help of an example, briefly state the meaning of parameter queries.
A parameter Query is a query that asks you for one or more pieces of information before displaying the data sheet. A parameter query prompts the user to enter Parameters, or criteria through an input box, for selecting a set of records.
A parameter query is useful when there is a need to repeat the same query with different criteria. The criteria, is not constant as in the case of the simple query. While extracting the transactions to prepare ledger accounts, the same set of queries need to be executed for different account codes. Consider the following SQL statement.
In the above query, 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.
Briefly state the purpose of functions in SQL environment.
SQL (Structured Query Language) is the most popular query language for relational DBMS, and most of the commercial relational database management system use SQL or a variant of SQL.
The original version of SQL was called SEQUEL. SEQUEL is a relational Query Language based on neither relational algebra nor relational calculus. It is a non-procedural language where the concept of universal quantifiers is not used.
A block-structured format of English key words is used in this query language. It is intended for interactive use by people who are not specialists in computers. There are three types of functions that are used in SQL statements.
Plus One Accountancy Accounting System Using Database Management System Four Mark Questions and Answers
What do you mean by programmed and casual reports?
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 so as to give confidence to the user, influence his behaviour and prompt him to take positive action. Accounting reports can be classified into two broad categories ie. Programmed Report and Casual Report.
1. Programmed Reports:
Programmed reports are reports which contain information useful for decision making in those situations which the users have anticipated to occur. There are two types of reports within this report type.
a. Scheduled Reports:
The reports, which are produced according to a given time frame, are called scheduled reports. The time frame may be daily, weekly, monthly, quarterly or yearly. Some Statement of Cash transactions (Cash book), Statement of Ageing Accounts, Closing Stock Report, Profit and Loss Account and Balance Sheet, etc.
b. On-Demand Reports:
The reports, which are generated only on the triggering of some event, are called on demand reports. Some examples of an demand reports are a Customer’s Statement of Account, Inventory Reorder Report, Stock in hand Report for a Selected Group of items, etc.
2. Casual Reports:
Casual reports are those reports, the need for which is not anticipated, the information content of which may be useful but casually required. These adhoc 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.
Describe and discuss the procedure of creating the receipts side of a cash book.
As it is known to all a Cash book is a type of accounting report. The procedure of creating the receipts side of cash book is same as reports generation. In the process of producing 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, Particulars and credit Balance with reference of each account where cash account is debited.
Receipt side of cash book deals with the cash receipts. In the process of creating receipts side we have to find out the amount and particulars of the transaction in which cash is received ie. cash account is debited. For creating it, first we have to find out these amounts and a particular set of SQL statement and save them into Database after giving particular name to each query.
After creating the SQL statement now we will start the process of MS Access Database. Select create report in design view and query (Last Saved). This respond to give blank report design dividend 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 the all fields from our Query and drag and drop them into the Detail section. By applying some arithmetical calculations provided by Access we get the receipt, side of cash book.
Plus One Accountancy Accounting System Using Database Management System Five Mark Questions and Answers
“MS-Access supports different data types”. What are the different data types?
The following are the different data types:
- Text: It is used for a string of character: Words or numbers that are not to be used in arithmetic calculations. The maximum length for a text field is 255 characters. It is the default data type. For example – Job name, Job department, Job type.
- Memo: It is used for storing comments and is capable of accommodating 65,536 characters.
- Number: It is meant to store numbers, which could be integers, long integers, bytes, single, double or decimal types. For example – Job unit, Job ID.
- Date / Time: It is used to store dates, time or a combination of both. For example – Job time.
- Currency: It is used for storing numbers in terms of Dollars, Rupees or other currencies.
- Auto Number: It is a numeric data automatically entered by access.
- Yes / No: It is to declare a logical field which may have only one of the two opposite values alternatively given as: Yes or No, On or Off, True or False.
- OLE object: OLE stands for Object Linking and Embedding. It refers to an object that could be a photograph, barcode image or another document created in another software application.
- Hyperlink: This data type is meant to store a Universal Resource Locator (URL) and e-mail addresses.
Briefly explain in steps the method of creating a query, using wizard.
In order to create a query using Wizard, the following steps are required.
1. Select Queries from Objects list given in LHS (Left Hand Side) of Database window.
2. Double click at Create Query by Using Wizard given on the RHS (Right Hand Side). 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 tables (or queries) being chosen represents the data source of the query being created. The fields 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.
3. Click at Next after the desired fields have been selected. If the selected fields include a number or currency field, the designer is prompted to choose an option button to specify whether the query to be created is a summary or detail query.
- If details 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.
4. Click at Next and specify the name of the query being created % Finish to save and execute the query. The results of the query are displayed in the datasheet view.
State the procedure for creating ledger in MS Access.
Financial Accounting Reports can be generated in MS Access easily. Ledger is one of the financial reports through which accounting transactions are shown in a particular account, during a given period of time. A ledger contains eight columns as follows:
For creating a ledger in MS Access it is necessary to retrieve a set of processed data recordes each of which provide information code (Account Number), Name of Account, Particulars, Date Debit Balance and Credit Balance with reference to each particular account.
For creating Ledger in MS Access we have to take the following steps: First of all, we will selecte the Design View method to generate a Ledger as follows:
1. Select Reports from the object list in Database window.
2. Click at New Button. This displays the New Report Windows.
3. Select Design view and Query L from combo control.
4. Click OK,
Now, the Access will display a blank report which will be there in three sections as Page header, Detail, and Page Footer. The above blank report will be displayed horizontally. Now it will show a list of available fields of Query L for embedding on to this blank design report.
5. Now it’s time to Click at properties of report and select data tab to define the record sources as Query L. This gives a list of available fields of Query L.
6. Now page header and footer will be choose by clicking at the part of report design. As we click, access will provide two more sections ie. Page header and Page footer.
7. Now we will describe the specifications by clicking at the icon of toolbar and pick up a label control to be placed at Page Header section and assign set it caption property to (Account name), Font size to 12. Font colour to Back, 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 label 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 test controls in Detail section.
12. Select the text controls and Amount field and modify their properties.
The above mentioned is the simple procedure through which we can create Ledger in MS Access.
Discuss with a set of inter-related data tables, the basics of creating queries in MS Access?
It is a well know fact 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 record containing field from across a number of data tables.
In other words SQL has statement for data defini-tion, query and update. Besides this, it has the ca-pability 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.
Some of the basics of creating queries in MS Access with a set of inter related data table are here with the help of the following statements.
If we talk about 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 amount of each group has been ascertaining and displayed.
We can take another example to understand it better:
In the above query, vouchers table, Account table and Account type table are joined on the basis of Debit Field, code field respectively to retrieve Code, Name, and Category of Expense account which have been debited.
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.
The following series of SQL Statement retrieve a record set of producing the payment side of cash book of Model II.
1. SQL Statement
2. SQL Statement
3. SQL Statement
This SQL Statement is source as Query D1.
4. SQL Statement
This SQL Statement is saved as Query D2.
5. SQL Statement
Plus One Accountancy Accounting System Using Database Management System Six Mark Questions and Answers
Briefly explain the set of SQL statements to produce the receipts side of a cash book for ModeII.
The following series of SQL statement retrieve a record set for producing the receipts side of cash book for ModeII.
1. To find the total amount by which the cash account is debited. In order to ascertain the total amount by which every transaction cash account has been debited, the following SQL statement is formed.
This SQL statement gives the code and amount from which cash is received. This SQL statement is saved as Query Cl.
2. To generate the total of Receipt side. The following SQL Statement is formed.
This SQL statement is save as Query C2.
3. To find the record set which consist of account code, Name of account, credit amount and date. The following SQL statement is formed.
Describe in steps the design view method to create a query in MS Access?
In order to create a query by design method, the following steps are required.
1. Select Queries from Objects list given in LHS of database window. Double click at Create Query by Using Design View given on the RHS.
2. Access responds by displaying a Select Query and Show Tables Window. The select query window is vertically divided into two panes, upper pane and lower pane. The upper pane is meant to display data sources (Tables or Existing Queries) and the lower pane, which 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 (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 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 Window. If closed, the show Table Window can be recalled by a right click at upper pane % show table.
3. Click at View item 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. The process is repeated for second and subsequent columns of grid to include more fields in the query. This process of selection constitutes the data items to be displayed by SELECT clause of SQL statement.
5. The name of table or query is displayed, in accordance with selection of fields. Such tables or queries constitute the data sources shown after FROM clauses of SQL statement. However, the initial selection 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 a summary query is created.
7. Click at row of grid to specify the sort order (Ascending or descending) on field(s). The selected fields for sort order are shown after ORDER BY clause of SQL statement in which ascending order is the choice of default.
8. Click at row to check for the selected field to be displayed in the query result. The field(s) may be selected only for the purpose of specifying the sort order or criteria.
9. Click at row ofthe grid to specify the criteria to limit the records to be displayed by the query being created. The specified criteria result in a conditional expression, which is shown after the WHERE clause of SQL statement.
10. Click File % Save (or Press Ctrl + S) to save a query. A dialog box prompts the user to specify the name of the query being created. By default a generic name appears which can be accepted or rewritten with the desired name.
Explain MS Access and its components.
MS Access is one of the popularly used Database Management System (DBMS) to create, store and manage database. It is popularly called Access. Access is functionally available with the following seven object classes. Each of these object classes is capable of creating their respective object replicas.
- Tables: This object class allows a database designer to create the data tables with their respective field names, data type, and properties.
- Queries: The stored data is processed using the query facility to produce desired information.
- Forms: This object class allows the designer to create an appropriate user interface to formally interact with back end database defined by the tables and queries.
- Reports: This object class is used to create various reports. Such reports are designed in access according to the requirement of end user.
- Pages: This object class is meant to create data access pages which can be posted on a website of an organisation using internet.
- Macros: A macro is a list of macro-oriented actions that runs as a unit.
- Modules: These are the foundation of any application and allow the designer to create a set of programming instructions called functions or sub-routines that can be used throughout the application.
Plus One Accountancy Accounting System Using Database Management System Eight Mark Questions and Answers
Discuss the SQL view method of creating a query.
SQL View Method:
A query may be directly specified in Select Query Pane by a right click at table pane % SQL view. The upper and lower panes of selected query window are substituted by a pane to specify the SQL Statement that is written by using keyboard.
The desired SQL statement is directly okeyed in on this pane and saved in the same manner as described for design method. While forming the SQL statement, the following clauses are normally used for generating information (or Select) queries.
This clause is used to specify the fields to display data or information. Consider the following SQL statement segment.
SELECT code, Name, Amount
The fields Code, Name and Amount after SELECT clause indicate the data items to be displayed by the query statement.
This clause is meant to indicate the source of data in terms of tables or queries or a combination of both. Two tables are joined by specifying a JOIN Clauuse based on a condition of Join. There can be three types of Join, Inner, Left and Right.
This join clause is meant to display only exactly matching records between two data sources. Consider the following SQL statement segment.
FROM Accounts INNER JOIN Account Type
ON (Catld = Type)
In the above statement, only those records of Accounts and Account Type table constitute the source of query data, which match exactly on Catld = Type.
With this Join, all the records in the primary table in the relationship are displayed irrespective whether there are matching records in the related table or not. Consider the following SQL statement segment.
FROM Accounts LEFT JOIN Account Type
ON (Catld = Type)
In the above statement, all records of Accounts along with matching records of Account Type table constitute the source of query data, the matching condition is Catld = Type.
With this join, all the records of related table in the relationship are displayed irrespective whether there are matching records in the primary table or not. Consider the following SQL statement.
FROM Accounts RIGHT JOIN Account Type
ON (Catld = Type)
In the above statement, all records of Account Type along with matching records of Accounts table constitute the source of query data. The matching condition is Catld = Type.
This clause in SQL statement is used to provide the condition to restrict the records to be returned by query. The resultant records of query must satisfy the condition which is specified after WHERE clause. This is meant to filter records returned by the query.
7. ORDER BY:
This clause is meant to 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. Consider the following SQL statement segment.
ORDER BY Type, Code
The above statement in the context of Accounts table implies that the resultant record set is ordered by the Type field of Accounts and within Type, by Code field of Accounts.
8. GROUP BY:
The group by clause is used in the SQL statement to enable grouping of records for creating summary query. The fields after GROUP BY clause constitute the basis of grouping for owhich summary results are obtained. Consider the following SQL statement.
In the above SQL statement, the GROUP BY clause uses Debit account codes as the basis for computing the sum of amount of voucher. The total amount, by which every transacted account has been debited, is given by this SQL statement. In this statement, sum of amount is found for each group of records formed using GROUP BY clause.
Describe the series of SQL statements to produce trial balance data base design for Model-ll is used.
The following series of SQL statements retrieve the record set for producing trial balance when database design for Model-ll is used. In addition to this, the accounts have been categorised within the trial balance according to the Account Type: Expenses, Revenues, Assets, and Liabilities.
1. To find the Total Amount by which the Accounts have been Debited:
The transacted accounts in design of Model-ll have been stored in AccCode of Vouchers Main and Code of Vouchers Detail:
The following SQL statement is formed to generate the relevant information from Vouchers Details.
Similarly, the following SQL statement is formed to generate the required information from Vouchers Main table.
WHERE Type = 1
GROUP BY AccCode;
Both the SQL statements are meant to extract similar sets of records, but from different sources. Therefore, the resultant record set of these SQL statements have been horizontally merged using UNION clause as shown below.
The above SQL statement is saved as Query 101 for its subsequent use. The total of debit amount in this query represents the Total with positive amounts.
2. To find the Total Amount by which the accounts have been credited:
In order to ascertain the total amount by which every transacted account has been credited, a query similar to that in (a) need to be formed.
This is achieved by substituting Debitfield in SELECT and GROUP BY clause by Credit field and the sum of amount generated by sum(Amount) is multiplied by -1 so that the final amount assigned to Total field is always negative. Accordingly, the following SQL statement is formed.
In the above SQL statement, the sum of amount has been multiplied by -1 to ensure that the amount of credit is always negative just as amount of debit is taken as positive. This query is saved as Query 102 for its subsequent use.
3. To find a collective record set of Accounts with their Debit and Credit totals:
A collective record set is generated by forming a union query between Query 101 and Query 102 to ensure that the debit and credit amount with respect to each account becomes available for generating the net amount.
Accordingly, the following SQL statement is formed.
The above SQL statement causes horizontal merger of record sets returned by Query101 and Query102. This SQL statement is saved as Query103 for its subsequent use in next query.
4. To find the Net Amount with which an account has been Debited or Credited:
To generate the net amount, an SQL statement similar to Query04 (designed for query(d) of Model-1) above, is formed as shown below, except that its source of data is Query103 instead of Query03.
This query is save as Query104 for its subsequent use in generating a record set, giving details of information for trial balance.
5. To find the Record set which consists of Account Code, Name of Account Debit Amount and Credit Amount:
This query, which is meant to provide relevant information to the trial balance report, is similar to Query 05 (designed and discussed in (e) of Model-1). Accordingly, the following SQL statement is formed by changing the source of data from Query 05 to Query 105 as shown below.
In above SQL statement, the results of Query104 and data stored in accounts table has been used. This SQL statement is saved as Queryl05 for providing source of information to Trial Balance Report.
Trial Balance with Sorting and Grouping Levels:
In order to prepare a trial balance with all the account duly grouped by and sorted within category of accounts, two additional queries (vi) and (vii) are required.
6. To Find the Record Set of Accounts with their Category and Category ID:
Accounts table is related to Account Type table vide Type field. The following SQL statement, using INNER JOIN clause, is formed to retrieve the relevant fields of various accounts.
SELECT Accounts.Code, Accounts. Name, Category, Catld FROM Accounts
INNER JOIN AccountType ON
Accounts.Type = Account type.Catld;
This SQL statement is saved as Query 106 for its subsequent use in next query.
7. To Find the Record Set consisting of Account Code, Name of Account, Debit Amount and Credit Amount along with Category Details:
This query, when compared with (v) above, reveals that two additional fields Category and Catld are required. Accordingly, the SQL statement stored as Query105 is modified by substituting Accounts table with Query106 to form the following statement.
SELECT a.Code, b.name AS (Name of Account), IIF (a.Net>0, a.Net, null) AS Debit, IIF (a.Net<0, abs(a.Net), null) AS Credit, Category, Catld FROM Queryl 04 AS a. Query106 AS b WHERE a.code = b.code;
This SQL statement is saved as Query107 to provide information details for designing trial balance with grouping and sorting of the accounts.
Using Model-III discuss the series of SQL statements to produce a trial balance up to a particular date.
The following series of SQL statement retrieves a record set for producing trial balance when data base is design for ModeII.
1. To find the total amount by which the account have been debited. The following SQL statement will be formed.
GROUP BY clause retrieves the rows of vouchers table accounts wise because the debit field refers to account code. This SQL statement is saved as Query 01. The total of debit amount in this query is given by total yield with positive amounts.
2. To find the total amount by which the accounts have been credited. The following SQL statement will be formed.
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 01.
3. To generate a collective record set of accounts with their debit and credit totals. Well this collective record set will be generated by executing a union query between Query01 and Query 02. The following SQL statement will be followed in this case.
FROM Query 02
This SQL statement is saved as Query 03.
4. To generate the net amount with which an account has been debited and credited. The following SQL statement will be formed
SELECT Code, Sum(Total) AS Net
FROM Query 03
GROUP BY Code
A positive net amount implies a debit and negative amount means a credit balance corresponding to an account code. This SQL statement is saved as Query 04 used in generating record set for trial balance.
5. To find that record set which consists of account code, name of account, debit amount and credit amount. The following SQL statement will be formed
SELECT a.Code, b. Name AS (Name of Account),
IIF (a.Net>0, a.Net, null) AS Debit,
IIF (a.Net>0, abs(a.Net), null) AS Credit,
FROM Query 04 AS a, Accounts ASb
This SQL statement is saved as Query05 for providing the necessary information content for Trial Balance Report.