Information Technology Center

Microsoft Access - Introductory level

Access Lesson Two

DESCRIPTION:

This lesson on Microsoft Access teaches how to create a database, add a table and populate a table with records.

SPECIFIC OBJECTIVES:

At the end of the lesson students should be able to :

• Create an access database
• Add a table
• Add records to a table
• Modify table structure
• Validate data items

PREREQUSITE KNOWLEDGE:

Before attempting this lesson you should know :

• The difference between data and information
• How to locate files in the document folder
• How to rename a file
• How to backup a file

Microsoft Access - Working with tables

2.0 What is a relational database?

A database is an organised collection of related data. This data can be about people, places, animals or things. A database that links together logically related data stored in several tables is know as a relational database.

Access tables

A table is a basic unit of a database. It is the most important object in the database as it contains data on the specific items being stored. Before we can create a table and add data to it we must first create the blank or empty database.

2.1 Creating a new Sample database

1. Click on the START button
2. Click on PROGRAMS
3. Select Microsoft Access
4. Choose create a database from scratch
5. Provide a File name : dbSample1
6. Specify a location from the options available

When a new database is created from scratch there are no objects in it. We must create a table before we can add data. We must examine the data to determine the structure needed to hold each data item.

Employee table details

When we layout the data in tabular format it is easy for us to determine exactly what fields we need for each table. For our sample database we will create our first table with only six fields. We will modify the table and add more fields later.

From the tabular data we determine the names and data type of each field as follows :

1. ID - text
2. Surname - text
3. First - text- first name
4. dob - date/time - date of birth
5. dpt - text - department
6. salary - currency

Field data types

After you select a data type from the drop down list the 'Field Properties' pane appears at the bottom of the window. The data type text is selected by default. The most common data types are listed below.

2.2 Creating a new table : tblEmployees

1. Ensure you are in Design View : Add the fields
2. Surname - text
3. First - text
4. dob - date/time
5. dpt - text
6. salary - currency

2.3 Adding records

Before you can add a record you must switch back to data sheet view

Add the seven records in table 2 above to the Clients table.

2.4 Data Validation

Switch the client table to design view before attempting the following :

1. Enter a new field ( gender - text)
2. Change the field length to - 1 character
3. Type in the Validation Rule box : M or F
4. Type in the Validation Tex box : M for male or F for Female ONLY
5. Switch to datasheet view
6. Enter a Gender for each record in the table
7. Try changing the gender of any record to the letter P

What happens if you try to change the gender in any record to any letter of the alphabet other than M or F? Give a reason for your answer.

Test Yourself