MICROSOFT ACCESS

INTRODUCTION

This is an application package that is designed for collection, organization, storage, updating and retrieval of data. It has been designed primarily for the purpose of keeping records. It is a relational database management system and forms part of the Microsoft office suite of programs. The ms access environment takes a different dimension from other ms applications. Being a relational database management system, all its content is organized into tables. The tables hold all the records in a database.

Definition of terms

Data- raw facts yet to be processed

Database (DB) - a collection of related records

Database Management System (DBMS) - software that is meant for organization and management of a digital database.

Relational Database Management System (RDBMS) – a DBMS that is organized into relations (Tables).

Records- a collection of facts about an entity

Entity-a person, animal, object, concept or abstraction about which information can be gathered

Attribute- a particular record about an entity

Tuple- another name for row

Field- another name for column

Column- a group of vertical cells

Row- a group of horizontal cells

Working in Access 2007

Creating a database

To create a database:

®     Select new from the office button

®     Select new

®     Select blank data base

®     Specify the database name

®     Select create (a database is created with a table by default)

Working with tables

Tables are the core database objects. Their purpose is to store information. The purpose

of every other database object is to interact in some manner with one or more tables. An

Access database can contain thousands of tables, and the number of records each table

can contain is limited more by the space available on your hard disk than by anything else

Every Access object has two or more views. For tables, the two most common views are

Datasheet view, in which you can see and modify the table’s data, and Design view, in

which you can see and modify the table’s structure. To open a table in Datasheet view,

either double-click its name in the Navigation Pane, or right-click its name and then click

Open. To open a table in Design view, right-click its name and then click Design View.

After an object is open, you can switch between views by clicking one of the View icons

in the lower-right corner of the program window, or by clicking the View arrow in the

Views group on the Home tab, and then selecting a view from the list. If you simply click

the View button Access switches between views in a manner that at times seems logical.

If the current view is not Design view, it switches to Design view. If you click it again, the

table switches to Datasheet view. When other database objects are active, clicking the

View switches between views in a similar manner.

When you view a table in Datasheet view, you see the table’s data in columns (fields) and

rows (records).

Creating a table

® Select the CREATE menu

® Select table

® Click VIEW

® Select design view and specify the table name

® Input the field names, the data types and their corresponding properties

NOTE: Field name refers to the column headers and data type refers to the kind of values to be entered in the column.

Deleting a table

® Close the table if it is open

® Right-click the table and select delete

® Select yes to confirm delete

Working with queries

You can locate specific information stored in a table, or in multiple tables, by creating a

Query specifying the parameters of the information you want to find. Running a query (also called querying the database displays a datasheet containing the records that fit your search criteria. You can use the query results as the basis for further analysis, create other Access objects (such as reports) from the results, or export the results to another format, such as an Excel spreadsheet or a Microsoft SharePoint list. If you will want to locate records matching the search criteria at any time in the future, you can save the query, and run it again fom the Queries section of the Navigation Pane.

Each time you run a query, Access evaluates the records in the specified table (or tables)

and displays the current subset of records that match the criteria you have defined.

Creating a query

When creating a query for multiple tables, there need to be a relationship between the tables. This is where the need for a primary key arises. The primary key identifies a particular record in a table. For a single table, this relationship is not needed since all the information drawn from the same table.

About relationships

One of the goals of good database design is to remove data redundancy (duplicate data). To achieve that goal, you divide your data into many subject-based tables so that each fact is represented only once. You then provide Microsoft Office Access 2007 with the means by which to bring the divided information back together — you do this by placing common fields in tables that are related. To do this step correctly, however, you must first understand the relationships between your tables, and then specify these relationships in your Office Access 2007 database.

After you have created a table for each subject in your database, you must provide Office Access 2007 with the means by which to bring that information back together again when needed. You do this by placing common fields in tables that are related, and by defining relationships between your tables. You can then create queries, forms, and reports that display information from several tables at once. This is made possible through the implementation of relationships.

To create query:

®     Select the create menu and choose Query wizard(the query wizard helps to create a query)

®     Select simple query

®     Specify the fields the query should contain from the table(s) and NEXT

®     Specify the query name and FINISH

Working with forms

A form is a database object that you can use to enter, edit, or display data from a table or a query. You can use forms to control access to data, such as which fields or rows of data are displayed. For example, certain users might need to see only several fields in a table with many fields. Providing those users with a form that contains just those fields makes it easier for them to use the database. You can also add buttons and other functionality to a form to automate frequently performed actions.

Think of forms as windows through which people see and reach your database. An effective form speeds the use of your database, because people don't have to search for what they need. A visually attractive form makes working with the database more pleasant and more efficient, and it can also help prevent incorrect data from being entered. Microsoft Office Access 2007 gives you new tools to help you create forms quickly, and provides new form types and features that improve the usability of your database.

To create a form:

®    Select the object(table/query) for the form

®    Select CREATE menu

Select FORM (This generates the form automatically).