Accounting System Using Database Management System Class 11 Notes Accountancy Chapter 15
A Database Management System (DBMS) is essentially a collection of inter-related data and |set of programmes to access this data. This selection of data is usually called the database. It can be regarded as an electronic filing cabinet. Relational Database Management System (RDBMS) enables us to manipulate data in a more sophisticated way. RDBMS avoids redundancy in data and defines the relationships between sets of data. The data stored in each table can be retrieved and updated based on data in another table.
MS ACCESS is a Database Management System (DBMS) that help in managing data that is stored in a computer database. Like the majority of database management systems in use today. Access is a relational database management system. Access database help in storing and managing information.
Access is Database Management System that allows the user to create and manipulate the database. Access is software that enables its user to perform various functions such as create, modify, delete and create data in tables of the database.
With Access, we can perform the following tasks:
- Organize data into manageable related units.
- Enter, locate and modify data.
- Extract subsets of data based on specific criteria.
- Create custom forms and reports.
- Automatic common database tasks.
- Graph data relationships.
In Access, the term database refers to a single file that contains a collection of information. A database consists of the following objects:
→ Tables: Tables store data in a row-and-column format similar to spreadsheets. Every table in a database focuses on one subject, for example, products, customers, students etc. Each row or record in the table is a unique instance of the subject of the table.
→ Queries: Queries extract data from a table on user-defined criteria. They enable us to view’ fields from more than one table.
→ Forms: Forms display and print data from a table(s) or a query based on a user-defined custom format. Forms enable us to view, edit and print data.
→ Reports: Reports display and print data from a table(s) or query based on a user-defined custom format.
→ Pages: Pages can be posted on a website of an organisation using the internet or sent via email to someone on the organisation network.
→ Macros: Macros automate common database action based on user-specified commands and events.
→ Modules: Modules automate complex operations and give a programme more control than macros. Modules are procedures written in Visual Basic or Application Programming Language.
→ Capabilities of MS Access: Access has certain capabilities, which bring it closer to an ideal Database Management System.
These capabilities are
- Storing the data in an organised manner.
- Enforcing data integrity constraints.
- Representing complex relationship among data.
- Providing for persistent storage of database object.
- Restricting unauthorised access to the database.
- Allowing fast retrieval of data with or without processing by using SQL.
- Flexibility to create multiple user interfaces.
- Providing for data showing and multi-user transaction processing.
- Supporting multiple views of advanced information. A new database can be created with the help of a computer wizard or without the help of a wizard.
Creating of Table in Access shows three columns i.e. Field Name, Data Type and description, which define the schema of a table is created. Field name refers to the column name of the table being created. Access supports different data types like Text, remove, Number, Date/ Time, Auto Number, Yes/No, OLE object and Hyperlink.
Access allows the designer to define the two types of properties of each column which are General properties and Lookup properties. General properties are Field size, format, Decimal places, Input Mask, Caption, Default value, validation Rule and Text, required and induced and Allow-zero length. Look up properties used by a field to find its values in another table, query or from a fixed list of values.
Forms are a way to view table data. We create the link between the form and its record source by using graphical objects called controls. We can use forms for a variety of purpose, for example, create a data entry form to enter data into a table or create a custom dialogue box to accept user input, and then carry out an action based on that input. The first step in designing a form is to create the table(s) or review the design of the tables. A forms problem often can be attributed to improperly designed table(s).
- Forms offer several advantages:
- Forms can display a complete record at a time, usually in vertical format.
- Forms allow us to customize the appearance in much more detail than a table with fonts, colours and graphics.
- Forms can display fields that the user cannot edit and can edit.
- Forms enable us to rearrange the field.
- Forms provide special field display functions, for example, drop-down list, word wrapping in fields and calculated fields.
- Forms enable us to automate tasks and display custom menus.
Every form control is complete objects with its independent set of properties, which determine the shape, size, behaviour and functionality of the object. Properties of these objects are of three types: Format, Data and other.
Format Properties:
- Format
- Decimal Places
- Caption
- Visible
- Layout Properties (Left, Top, Width, Height)
- Back Colours and Style
- Special Effects
- Border Properties (Style, Colours, Effect)
- Fore Colour
- Font Properties (Name, Size etc.)
- Text Align
- Margins (Left, Right, Top, etc.)
- Line Spacing
- Display When
- Scroll Bars
Data Properties:
- Control Source
- Input Mask
- Default Value
- Validation (Rule and Text)
- Enabled and Locked
Other Properties:
- Name
- Status Bar Text
- Enter Key Behaviour
- Allow AutoCorrect
- Vertical
- Default
- Tab Stop
- Tab Index
- Shortcut Menu
- Control Tip Text
- Help Context ID
Access provides for a number of controls and more can be added using the added-in-manager in Tools of the menu bar. Bound, Unbound and Calculated are three types of control.
Some of the common controls important for designing a form are:
- Label
- Text Box
- List Box
- Combo Box
- Sub-Form
(a) Source Object
(b) Link Child Fields
(c) Link Master Fields - Option Group
- Command Button
(a) Record Navigation
(b) Record Operation
(c) Form Operation
(d) Report Operation
(e) Application
(f) Miscellaneous - Control Wizard
Creation of Form can be done by
(a) Design,
(b) Wizard.
One of the most useful features of modern database applications is the query, which provides us with a way to question our database. The result of the 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 extract 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 of the object or the report.
For each type of query, we can specify the query parameters that prompt the user to specify the query criteria before the query executes.
There are several types of queries in Access that are used to generate information:
(a) Simple Query
(b) Parameter Queries
(c) Summary Queries
There are three methods by which any of the above queries can be created in Access:
(a) Wizard Method
(b) Design Method
(c) SQL view Method
An accounting reporting capability is incomplete as reporting is one of the main purposes for which an accounting system is designed and operated upon. The output of the accounting system takes the form of accounting reports.
Accounting report display information that is acquired from data processing and transformation in an organised manner. Reports sent to reduce the level of uncertainly associated with decision-makers and also influence their positive actions. The output of the computerised is accounting reports. Financial accounting Reports such a Cash Books, Bank Book, Ledger and Trial Balance may be generated in Access by adhering to the report generation process.
Generating accounting reports in Access involves three steps:
- Designing the report
- Identifying accounting information queries
- Using the record set o final SQL
There are two types of formats for presenting information through a report:
- Columns Report Format
- Tabular Report Format
A report in Access is designed using seven sections which taken together constitutes the structure of report design.
- Report Header
- Page Header
- Group Header
- Details
- Group Footer
- Page Footer
- Report Footer
The Report can be created by using any of the following methods:
(a) Auto Report Method
(b) Wizard Method
(c) Design View Method
The design of the report created by any of the methods may be improved upon by making the following addition and modification to the report.
- Adding Dates and Page Number.
- Adding and Deleting Report Contbls
- Conditionally Formatting Report Control
- Grouping Levels and Sorting Order
The design and a generated report ai saved for future use and reference. The generated report may also b exported for use by others.
(a) Saving and Exporting Report ejects in Access
(b) Saving as Snapshot
(c) Exporting to Excel
(d) Exporting to MS WORD
(e) Printing a Report
(f) E-Mailing a Report
Financial Accounting Reports such as Cash Book, Bank Book, Ledger Accounts and Trial Balance may be generated on Access by adhering to the report generation process.