Revision Notes

Class-11 Accountancy
Chapter 15 - Accounting System Using Database Management System

A. Database management system (DBMS)

A database management system is a program that helps in the management of a database
containing a large set of organised data. It also assists in running operations on the stored
data as required by the users of the stored data.

B. MS Access and Its Components

One of the most popular DBMS used to create and manage a database is Microsoft
Access. It is commonly called MS Access, or just Access. Access is used to create
components. These components are objects, and a collection of such similar objects
Forms a class.
In MS Access, there are seven object classes having varied functions. These are
described as follows:
1) Tables: This object class enables the designer of the database to create data tables
with their respective field names, data types and properties.
2) Queries: This object class helps to create the SQL compatible query statement, store
data, and retrieve data and information. The SQL compatible query statement can be
created with or without the help of a Graphic User Interface (GUI) for defining the tables.
3) Forms: This object class enables the designer of the database to create an interactive
user interface such that the users can interact with the back-end database, which is
defined by tables and queries.
4) Reports: This object class helps to create various reports according to the
requirements of the user. Such reports are designed using the information based on the
tables and queries.
5) Pages: This object class helps to create the Data Access pages. These pages can be
posted either on a web site, or sent through email to someone within the organisation’s
network.
6) Macros: Macro-oriented actions are objects that use individual instructions in macro
programming. These actions are manipulated in macro programming. The list of such
macro-oriented actions that run as a unit is called a macro. Access is capable of macro
programming.

Class XI Accountancy www.vedantu.com 1

7) Modules: This object class constitutes the foundations of an application, designed to
enable the designer to create a set of programming instructions. These sets of instructions
are called functions or subroutines and can be used throughout the application.

C. Access Basics for Creating a Database

When a new database is created from the scratch without the help of database wizard
(that is an automated process in Access), the following steps are required:
1) First, open the Access window, select the blank Access database, and click on the OK
button.
2) The File New Database dialog box appears. Enter the file name and location for the
database, and then click on the Create button.
3) The task pane can be opened (if not opened already) by selecting File from the menu
bar, and clicking new to open the task pane and create a new database.

D. Creating of Tables in Access

The understanding of the procedure and the components in Access are necessary to create
desired tables. The following steps need to be followed to create tables in access.
In Access, click the Tables object, and then double click on create table by design view.
This opens a table window. The upper part of the window has three tabs: Field Name,
Data Type and Description. These can be used to define the particular schema of the table
being created, where each row corresponds to a particular column.
Two primary properties of the columns of a table are the field name and data type. These
are described as below:
1) Field name
The field name refers to the name of the table that is being created. It is used to define the
column that is being created, which is followed by the data type of that column.

2) Data Types
Numerous data types are supported by Access. These are described as below:
(a) Text: The text data type is used to define a string of characters, which includes words
and numbers that are not required in any calculations. A maximum of 255 characters can
be used in a text field.

Class XI Accountancy www.vedantu.com 2

(b) Memo: This data type is used to store comments. A maximum of 65,636 characters
can be used in a field with this data type. However, if a field has this data type, then it
cannot be amended to sort or filter data records.
(c) Number: The numbers data type is used to define numbers like integers, bytes,
single, double (for storing values in decimal point with greater magnitude and precision),
or decimal types.
(d) Date/Time: The Date/Time data type enables storing dates, times, or both in the
field.
(e) Currency: The Currency data type is used to store numbers which define currency,
such as Dollars, Rupees, Euros, etc.
(f) AutoNumber: This data type is the numeric data which Access automatically enters.
This data type is especially important when the individual fields or their combination in a
table is not unique.
(g) Yes/No: This data type is used to create a logical field, which contains only one of
two opposite values. The opposite values may be Yes/No, On/Off, True/False, etc.
(h) OLE Object: The Object Linking and Embedding (ODE) object can be any
photograph, barcode image, or document that is created in some other software
application.
(i) Hyperlink: The Hyperlink data type is used for storing the Universal Resource
Locator (URL) and e-mail addresses.

3) Properties
After specifying the data type of the column, Access enables the designer of the database
to define the properties of each of the columns. These properties can be categorised as
General and Look up.
(a) General
The general properties in context of text and data type are:
• Field Size: The field size property of a text field refers to the maximum number of
characters that can be used in the column. The field size property of a number field refers
to the type of numbers that are stored according to the requirements.
• Format: The Format property is used to define how the contents in the fields are
displayed. In Access, there are certain standard Format types that can be used.
• Decimal places property: The decimal places property is the property applied to
single, double, or decimal types of numbers.

Class XI Accountancy www.vedantu.com 3

• Input mask: Input masks are the Formats used to enter data that includes placeholders
or punctuations. Input masks can function only in case of text or data type of fields.
• Caption: The label used for the fields in the datasheet view, on Forms, or on reports is
called caption. In case the caption property is set as blank, the default caption is used as
the field name to label the field.
• Default Value: The default value property is used to specify the value for new entries
in the data records. When a data item is entered, the default value can always be
overwritten by the operator.
• Validation Rule and Text: The checking of data in order to eliminate the incorrect
entries is called validation. For this property, the validation criteria can be specified.
• Required and Indexed: The Required property is used to indicate whether entering
data in a field is mandatory or optional. This property provides a logical value Yes or No.
If the property of a field is set to Yes, the user has to enter data in the field before saving
the data record. If the property of a field is set to No, entering data in the field is optional.
• Allow-Zero Length: The Allow-Zero length property can be used only in case of text
fields. This property provides a logical value Yes or No, which determines if a text spring
having zero length is a valid entry or not.
(b) Look up
The lookup feature enables a field to find its values in any other table, query, or a
particular list of values, which can be displayed using a list box or a combo box. The
default display control of the lookup feature is a text box.
In the case of list box or combo box, some additional properties are used to specify the
bound column, that is the column whose values are copied to this field as references. The
column count property determines the number of columns that appear in the list box or
the combo box.
• The steps mentioned to define a column have to be repeated to define every column that
is to be created in a particular table.
• After the columns are defined, any of the columns that will have unique data values can
be set as the primary key column of the table. This can be done by right clicking at the
field which is to be specified as primary key, and then selecting the primary key item.
• The table design can be saved by choosing the File option in the menu bar, and clicking
on Save. The save dialog box displays a generic default name for the table, which can be
edited as required. The table gets created and then appears as a list to the right of the
table object.
• The remaining tables in the database design can be created in a similar way.

Class XI Accountancy www.vedantu.com 4

E. Database Design for Simple Transaction Vouchers
The five data tables are: Employees, Accounts, Vouchers, Support and Account Type.
These are described below in terms of their storage structure, i.e. column names, data
types and properties.
1) Account Type
The Account Type table has two columns. These are CatId and Category.
(a) CatId: This column specifies the value that identifies the category of accounts.
(b) Category: This column is used for the storing of the string of characters. These
express the category of accounts. For example: These express the account as Expenses
account, Asset account, etc.

2) Accounts
The Accounts table has three columns. These are Code, Name and Type.
(a) Code: This column stores the code (the unique account number which identifies an
account of the account. Since no calculations are required on this column, its data type is
text.
(b) Name: The name of an account corresponding to the account code is stored in the
name column.
(c) Type: Each account has to be defined as a particular account type in the Account
Type table. The Type field acts as a foreign key to the reference CatId field of the
Account Type table.

3) Employees
The Employees table is used for storing the data related to the employees in the
organisation. This table contains the below six columns:
(a) EmpId: This column stores the EmpId of each employee, that is the unique data
value assigned to identify each employee.
(b) Fname: This column stores the first name of each employee. Since no calculations
are required on this column, its data type is text.
(c) Mname: This column stores the middle name of each employee.
(d) Lname: This column stores the last name of each employee.
(e) Phone No: This column stores the phone number of each employee. Its data type is
text with field size set to 12.

Class XI Accountancy www.vedantu.com 5

(f) SuperId: This column keeps and stores the EmpId of the supervisor or the immediate
superior of the specific employee.

4) Vouchers
The Vouchers table is used for storing the data related to the transactions as written in the
vouchers. This table contains the following nine columns:
(a) Vno: This column stores the store voucher number of each voucher, that is the
distinct identity of each voucher. Its data type can be set to number if numeric digits are
to be assigned to the vouchers.
(b) Debit: This column stores the code corresponding to the account which is debited
while recording the transaction.
(c) Amount: This column stores the amount credited or debited to the accounts in the
transaction.
(d) Vdate: This column stores the date of the transaction.
(e) Credit: This column stores the code corresponding to the account which is credited
while recording the transaction.
(f) Narration: This column stores the narration describing the transaction in the voucher.
Its data type can be set to text with a field size set to 100.
(g) Prep By: This column stores the identity, that is the EmpId of the employee (as
described in the Employees table) who has prepared the voucher.
(h) AuthBy: This column stores the identity, that is the EmpId of the employee (as
described in the Employees table) who has authorised the voucher.
(i) Support: This column stores the details related to the support documents attached to a
voucher.

F. Modified Design for Implementing Compound Vouchers

1) Vouchers Main
This table stores one record for each transaction. The rows of this table include the data
items of the vouchers that lie outside its grid. It includes six columns, that is Vno,
AccCode, vdate, PrepBy, AuthBy and Type.
• AccCode: This column stores the code of the complementing account. In case of a debit
voucher, the complementing account is the credit account. Similarly, in the case of a
credit voucher, the complementing account is the debit account.

Class XI Accountancy www.vedantu.com 6

• Type: This column stores the value 0 in case of a debit voucher, and the value 1 in case
of a credit voucher. Its data type is set to Number with a field size set to byte.
2) VouchersDetail
This table keeps and stores the data items in the vouchers that come into view within the
grid of the debit or credit voucher. It does not store the total amount because the total
amount is derived data, calculated by adding the written amounts. It includes five
columns, that is Vno, Sno, Code, Amount and Narration.
• Vno: This column stores the voucher number of the debit or credit record of the
VouchersMain table to which the entries of the vouchersDetails table relate.
• Sno: This column stores the serial numbers 1, 2, 3 and so on. These serial numbers
correspond to specific debit or credit entries that appear within the grid of the debit or
credit voucher.
• Code: This column stores the code of the account. In case of a debit voucher, the
account is the debit account. Similarly, in case of a credit voucher, the account is the
credit account.

G. Vouchers Using Forms

The following section involves the creation of vouchers using Forms, transformation of
the voucher designs in terms of Access objects and properties, and specifies the
procedure for the creation of Forms for vouchers.

H. Access Basics for Creating Forms

Some of the purposes for which a Form is designed, developed, and used in Access are:
1) Data Entry: A Form can be designed and used to enter, edit, and display data.
2) Application flow: A Form can be designed and used to navigate through an
application.
3) Custom Dialog Box: A Form can be designed and used to provide certain messages to
the user, or to receive parameters from the user, which are required to execute a query
based on parameters.
4) Printing information: A Form can also be used to print information and thus, provide
hard copies of information related to data entry.

I. Tool Box and Form Controls

Class XI Accountancy www.vedantu.com 7

A tool box contains a number of visual objects or controls that are embedded on a Form
to provide meaning and functionality to the Form. Several controls having distinct
functionalities and properties are used to design a Form.

J. Properties of Controls
Each control in the tool box used to design the Form is a complete object having distinct
functionalities and properties. These properties define the shape, size, behaviour, and
functionality of the object. The properties are categorised into format, data, and other
properties, which are described below:
1) Format Properties
• Format: This property is used to define the manner in which the data is to be displayed
in the control. This property is obtained from its underlying data source.
• Caption: This property specifies the printed matter that appears on the face of the
control. It applies to labels, command buttons, and toggle buttons.
• Visible: This property is used to specify whether the controls placed should appear or
be hidden when the Form is opened.
• Fore Colour: This property is used to assign any colour to the text that is being
formatted.
• Layout Properties (Left, Top, Width, Height): The Layout properties are used to
decide the size and position of the controls that are embedded to the Form.

2) Data Properties
• Control Source: This property is used to specify the field from the record source
associated with the specific control. This property specifies the record source underlying
the Form by default.
• Input Mask: This property is meant to affect the data entry format used in the control.
• Default Value: This property is used to provide the values assigned to the fields when
new data records are added. It is obtained from the underlying field of record source with
which the control is bound.
• Validation (Rule and Text): This property is used to perform the validation function at
Form level in case of controls.
• Enabled and Locked: This property determines whether focus is permitted on the
control or not. If this property is set to No, then the control appears dimmed and no
mouse action can be performed on that control.

Class XI Accountancy www.vedantu.com 8

3) Other Properties
• Name: This property is used by a designer to provide a customised name to a particular
control. The name that is assigned must be purpose oriented such that the structured
Form becomes self-documenting.
• Status Bar Text: This property is used to specify the text messages displayed in the
status bar when focus is permitted on a control.
• Enter Key Behaviour: This property determines the function of the Enter key. The
Enter key can be used either to add a new line to the current control, or move the cursor
to the next control.
• Allow AutoCorrect: This property can be set to Yes to turn on the auto correction
feature. This feature corrects common spelling errors and types automatically, and is used
when the Text control for Memo field is being used.
• Vertical: This property determines the direction of the text in the control. If set to No,
the text appears horizontally. If set to Yes, the text in the control appears vertically, that
is rotated at 90 degrees.

K. Common Controls in MS Access

There are a number of controls in Access that can be used to design a Form. More
controls can be embedded to the Form using the add-in manager in Tools of menu bar.
The three types of controls are: Bound, unbound and calculated.
Some common controls essential for designing a Form are described as follows:
1) Label: This control is used for writing dark prints on the Form. Some dark prints
written using this control include Transaction Voucher, Voucher No, serial number,
Debit, Credit, Amount, Narration, Authorised By, Prepared By, etc.
2) Text Box: This control is used to add a blank area which can be used to enter data. For
example: A blank space added next to an amount label can be used to receive the value of
the amount of the voucher.
3) List Box: This control allows a user to choose from a limited set of values. For
example, the domain of the values is limited and predefined.
4) Combo Box: This control allows a user to choose an item from a list, or enter a value
using a keyboard. Therefore, this control features a combination of the features of the text
box and list box.
5) Sub-Form: Many forms are generally based on more than one table. The Subform is a
Form within a Form used to tabulate and present the records of such tables.

Class XI Accountancy www.vedantu.com 9

L. Creating Tables in Access
In Access, a table refers to an organized structure that holds information. It includes
fields of information in which records of the table are entered. A record refers to the
collection of related fields that describe a single item. A field refers to the column that
consists of one category of information.
The tables in Access can be created by clicking the Create tab within the Design view in
the Ribbon, and then clicking on the Table Design button in the Tables group. This
displays a table in the area containing the tabbed documents.
When in Design View, only the representation of the structure of the table, and not the
actual data stored in the table can be seen. This provides more control over the field
properties to the designer as compared to when using the Datasheet View for creating a
table.
In the window, there are two panes. The top contains a design grid used to enter names of
the fields and data types. The design grid also contains a small box called the row
selector button, which can be clicked to select the entire row. Below the design grid,
there is a properties section.

M. Create Tables in Access – Tutorial

The following picture depicts a new table being created by a user. It also shows the user
assigning a data type to a particular field in the Design View in Access 2016.

Class XI Accountancy www.vedantu.com 10

In Design View, tables can be created by typing the unique, brief, and descriptive field
names in the Field Name column. The field name should not contain spaces. Capitalizing
the first letter of each word, or an underscore can be used to separate the words within the
field name without adding spaces.
The order in which the fields appear is the same order that appears from left to right in
the Datasheet View of the table.
In the next step, data types must be assigned to each field using the drop-down that
appears when the “Data Type” column to the right of the field name is clicked. The data
type tells Access the type of data that will be stored in the field.
In relational databases, it is quicker to index and query the tables if many varied kinds of
data types exist in the table. The data types can be changed and reviewed when assigning
to the fields.

N. Field Data Types You Can Assign When You Create Tables in Access Data
Types in Access
1) Short Text: This data type includes text or combination of text, numbers, or some
other information. The maximum length of text that can be added in this data type is 255
characters.
2) Long Text: This data type includes text or combination of text, numbers, or some
other information. The maximum length of text that can be added in this data type is
65,535 characters.

Class XI Accountancy www.vedantu.com 11

3) Number: This data type includes numeric data on which calculations are to be
performed. This does not include phone numbers or zip codes because no calculations are
performed on such numeric data.
4) Date/Time: The Date/Time data type enables you to store dates, times, or both in the
field.
5) Currency: The Currency data type is used to store numbers formatted as currency,
such as Dollars, Rupees, Euros, etc.
6) AutoNumber: This data type assigns a unique numeric ID to the records entered in
the table. A field having this data type can be used as the primary key field.
7) Yes/No: This data type is used to create a logical field, containing only two possible
values in the field, such as Yes/No, On/Off, True/False, etc.
8) OLE Object (Desktop Only): This data type connects to objects in other software
applications, such as photographs, barcode image, or document. It can only be used in
Desktop database files.
9) Hyperlink: This data type contains the hyperlink to an address on the World Wide
Web, such as the URL or e-mail addresses.
10) Attachment (Desktop Only): This data type can be used to attach any type of
supported file like image, spreadsheet, etc. It can only be used in Desktop database files,
and gives more flexibility than the OLE object field.
11) Calculated: This data type creates a calculated field. The values in these fields are
derived as a result of a function on other table fields.
12) Lookup Wizard: This data type instructs how to set up a lookup field, which will
contain values from another table, query, or list of values.
13) Image (Web App Only): This data type is used to store data in web apps.

O. Create Tables in Access – Tutorial:

The following picture displays the field names, data types, and a primary key of a table in
Design View in Access.

Class XI Accountancy www.vedantu.com 12

The Field Properties section is below the design grid. This section can be used to set the
properties of the selected field. The properties are displayed in the General and Lookup
tabs, and can be edited or set by changing the values.
After finishing the creation of the table, the primary key for the table needs to be set. This
key identifies each table record as unique. Generally, the primary key field is there
naturally in the data. In case the field is not there, the AutoNumber field can be added in
the table.
To set a particular field as the primary key, select the row selector, click on the Design
tab under Table tools in the ribbon, and then click on the Primary Key button in the Tools
group.
Finally, save the table using the Save button in the Quick Access toolbar. In the save as
dialog box, set a name for the table, and click on the OK button.

P. Create Tables in Access Using “Design View”: Instructions

1) Click on the Create tab in the Ribbon.
2) In the Tables group, click on the Table Design button.
3) A new table appears in the area containing the tabbed documents.
4) Enter the name of the field into the Field Name column.
5) Press the Tab key to shift the cursor to the next column to the right.
6) Next, assign data types to the fields by using the drop-down menu in the Data Type
column.
7) Press the Tab key to shift the cursor to the Description column.
8) Enter a description of the data that is stored in the field. It is not necessary to enter a
description if not required.
9) Press the Tab key to shift the cursor down to the next row.

Class XI Accountancy www.vedantu.com 13

10) Perform steps 4 through 9 again until all the necessary table fields have names, data
types, and descriptions as required.
11) Next, select the row selector containing the field that is to be set as the primary key
for the table.
12) In the Table tools tab in the ribbon, click on the Design tab.
13) Click on the Primary Key button in the Tools group.
14) Save the table by clicking on the Save button in the quick access toolbar.
15) Add a name to the table in the dialog box that appears.
16) Click the OK button.

Class XI Accountancy www.vedantu.com 14