MICROSOFT ACCESS (DATABASE MANAGEMENT SYSTEM SOFTWARE)

INTRODUCTION

Information plays vital role in today’s changing technology environment. A person can do correct decision if he/she gets correct information. If planning officers, managers, decision level people, etc. get correct information whenever they desired then they can drive country in the correct developing path. Data are the source of information. Data can be collected from different sources. The data can be stored and managed manually or electronically. The data need to be stored in such a way that they can be easily retrieved and updated whenever needed. To manage data efficiently and effectively a database management system is required. MS-Access is one of the popular database management system software.

DATABASE AND DATABASE MANAGEMENT SYSTEM
A database is a collection of data related to a particular subject or purpose. A database organizes data in easily accessible manner. A user can retrieve information from the database in an effective and efficient manner. The telephone directly, flight schedule, library catalogue, census, railway timetable, encyclopedia, dictionaries, etc. are some examples of database. The telephone directory gives information about names, addresses, and telephone numbers of people. A library catalogue has information regarding all the books available in the library i.e. title, author, publisher, edition, or which shelf it is available on.

A system or mechanism that creates, modifies, organizes and retrieves information is called Database Management System (DBMS). Database management system software like MS-ACCESS, ORACLE, DBASE, FOXPRO, SYBASE, etc. are used for the database management. These database management software help to store, organize and retrieve information much more efficiently.
The basic components of a DBMS are table, field and record.

Advantages of Database Management System
Database Management System is not easy to manage data manually in the case of large database. Manual database needs more paper spaces for storage and more people for managing database. Retrieveing, updating, adding, etc. in a large database takes more time. To overcome the shortcoming of a manual database management system one can use a computer for managing databases.

Some of the major advantages of a computerized DBMS are listed below.
• Database Management System allows us to store and handle a large amount of data easily on computers.

• The required information can be retrieved efficiently in short period of time.
• Database Management System reduces data redundancy. Database Management System reduces duplication or repetition of the same data in different tables. Database Management System does not allow us to enter same record twice in a table.
• Updating a database regularly from a single place is possible.
• Any numbers of different reports can be generated quickly form one database .
• Database Management System reduces inconsistency. When data in a table is updated, the related data in other tables also updated automatically.
• Database Management System provides high data security facility. Unauthorized users cannot access to data.
• Database Management System provides data sharing facility. The database stored at one place (centralized location) is available to different authorized users.

INTRODUCTION TO MS-ACCESS
MS-Access is the Relational Database Management System Software (RDBMS) used to store and manage computer-based databases on desktop computers. MS-Access allows you to store data in multiple tables and these tables can be linked together. MS-Access allows you retrieve information from a table or the linked tables. MS-Access provides a set of components or objects in order to manage database. These all components of MS-Access are stored in a single file called Database file. The database file of MS-Access has .MDB extension.

Objects of MS-Access
Some of the commonly used objects are Table, Query, Form and Report.
a. Table
The tables are the containers for all the data in a database i.e. all the data are stored in the table. In a table, data are arranged in columns and rows. A database can contain one or more tables.
b. Query
By using query, one can retrieve the desired information for a specific purpose. A query can be based on a table or multiple linked tables. Queries can be used as the source of records for forms and reports.

c. Form
A form is a user friendly interface used to enter, view and edit data of a table. A form can be based on a table, multiple linked tables or a query. Form can display a complete record at a time.

d. Report
A report presents information according to the specifications. Report is used to format information ready for printing. Some examples of reports are Mark Sheet, Salary Sheet, Telephone list and Mailing labels.

PRIMARY KEY
Every table should have at least one field that has a unique value for each record. This unique identifier field is known as the primary key field. The primary key ensures that unique data is entered for a field and the field is not left blank (null). With the help of primary key you know the record belongs to whom. Primary key is the filed that does not allow duplicate records in a table.

A primary key may be chosen out of the existing fields in a table, or a new field can be created specifically for this purpose. Symbol number of students, registration number of students, account number and receipt number are some suitable fields for primary key.

NAMING CONVENTION OF FIELD
a. Field name can be up to 64 characters long.
b. Field name can have letters, numbers, spaces or punctuation marks.
c. Field can’t contain a period (.), square bracket ([]), and exclamation mark (!).
d. Field name can’t begin with space i.e. field should begin with a valid character or number.
e. Field name must be unique in a table.

DATA TYPES IN MS-ACCESS
A characteristic of a field that determines what kind of data it can store is known as Field Data Type. Microsoft Access has following ten different field data types.

1. Text
Text field data type stores alphanumeric characters. Text can hold up to 255 characters. The default size of this field is 50. Numbers stored in this field type can not be used for calculations.

2. Memo
Memo type of field holds a large amount of alphanumeric characters. A Memo field is used when you need to store more than 255 characters. Normally, notes or descriptions are stored in this field.

3. Number
Number type of field holds only numbers or numbers with decimals on which calculations have to be performed. Number does not allow any other characters except number. What type of number, it can store, depends on the option selected from the field size list. The options available are:

• Byte: Byte stores numbers from 0 to 255 without decimals. Bytes occupies one byte.
• Integer: Integer stores numbers from -32768 to 32767 without decimals. Integer occupies two bytes.
• Long Integer: Long Integer stores numbers from -2 billion to +2 billion approximately without decimals. Long Integer is the default number field size. Long Integer occupies 4 bytes.
• Single Precision: Single Precision stores single precision floating point numbers from -3.402823E38 to 3.402823E38. Single Precision occupies 4 bytes.
• Double precision: Double precision stores double precision floating point number. Double precision occupies 8 bytes.

4. Date/Time
This field type stores data and time. Date/Time occupies 8 bytes.

5. Currency
Currency field allows storing monetary values such as cost. Currency field can be used in arithmetic calculation. Currency occupies 8 bytes.

6. AutoNumber
AutoNumber generates the serial number automatically. A value of a AutoNumber fields can not be updated. By default AutoNumber starts with one and AutoNumber has increment of one. In old version this field is known as Counter.

7. Yes/NO
Yes/NO field is used for data that can be only one of two possible values, such as Yes/NO, True/False, On/Off. Yes/NO data type is also known as Boolean data type.

8. OLE Object
OLE Object field data type allows you to store pictures, charts, audio and video.

9. Hyperlink
Hyperlink field holds hyperlink addresses of web sites, database objects or other files. Hyperlink stores up to 64,000 characters.

10. Lookup Wizard
The Lookup Wizard creates a field that allows you to choose a value from another table or from a list of values by using a list or combo box. When you select this data type, a wizard helps you to create the list. After you complete the wizard, MS-Access sets the data type based on the values selected in the wizard.