Information Technology Center

Lesson One - Working with tabular data

DESCRIPTION:

This lesson on Microsoft Access identifys what a database is and the six objects that make up an access database.

SPECIFIC OBJECTIVES:

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

• Present data in tabular form
• Plan a simple access table
• Identify data types for fields

PREREQUSITE KNOWLEDGE:

Before attempting this lesson you should know :

• How to locate files in the document folder
• How to use the basic features of Ms Word
• Basic knowledge of Excel can be helpful
• Know the difference between files and records

About Microsoft Access

Exercise 1.0

Microsot Access is a powerful database application that is part of the Office application suite. It allows users to enter, query, manipulate and print databases with thousands of records. While data can be imported from MS Excel and exported to Excel with little effort Access and Excel are different applications and this should be kept in mind when learning access if you come from an excel background.

What is a database?

A database is an organised collection of related data. This data can be about people, places, animals or things. We collect data all the time and once it is organised in some meaningful way we have a database. Do you keep a list with the names and numbers of friends and family written down somewhere? Then that is a manual database. It is not difficult to put that information into an electronic database such as microsoft access.

Advantages to using a database

1. Electronic forms make data entry simple
2. The data can be validated at data entry time.
3. Easy to enter and update the data when needed
4. Related items of data can be linked to generate reports
5. Can be used for personal as well as business records

Disadvantages to using a database

1. Computers can break down causing you revert to a manual system
2. Users must be trained and that takes time and money
3. Data can be corrupted and or stolen if safeguards are not in place

If you have few records then an electronic database will not be needed. However, if you are running a small business and you want to be ready for expansion then an electronic database can provide you with many benefits and help you provide good customer service.

Objects of Access

An access database can contain six objects. 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. The data we collect must be placed into tables first before we can display and manipulate it.

1. tables - which hold the data
2. queries - which organises and displays the data
3. forms - used to enter and view data
4. reports - designed to be printed
5. macros - to automate tasks
6. modules - to develop applications

Planning

Before going to a computer to set up a database you must plan what you are going to do. Planning is key because you must consider how best to display your data. The objective must be to have as little duplication of data as possible. Tables with related fields must be linked in a meaningful way.

Terms to master

Each term will be explained in detail as the lesson progresses.

1. table
2. field
3. attributes
4. primary key
5. validation rule and message

Data and tables

Sample data

Remember tables are the most important object in a database so it is most important to design tables carefully to ensure your database meets its objective.

Here is some details of three employees to be placed into an access table :

Scott, Randolph ID 3002 - 12/03/1995, male, finance, $6,000
James, Charlotte ID 2007 - 24/08/1997, female, sales, $5,000
Sing, Ricky ID 3005 - 4/08/2000, male, distribution, $4,000

Examine the data carefully and note the following :

1. Data is provided for three employees
2. We have a separate record for each employee.
3. The surname is recorded first followed by the first name
4. Each employee is assigned an employee ID
5. The gender of each employee forms part of each employee's record
6. Each employee is assigned to a department
7. For each employee we record seven data items

Each data item must go into a separate field. All the items of a field are of the same data type. The last item of data records the monthly salary so only figures that represent salary must be placed into that field. The salary field will not accept a date nor will it accept text data.

Test Yourself

Access Activity 1.0

Rule up a page in your notebook with seven columns

1. Enter at the top of each column ( surname, first, ID, dob, gender, dpt, salary)
2. Enter each employee record on a separate row
3. Add a new female employee with ID number 2009
4. Fill in the details for this new employee
5. Choose an existing department to add the employee to
6. Compare employee ID numbers. What do you observe?
7. What do you think the field name dob stands for?