MS ACCESS

MS Access is a computerized database management software, which allows you to collect and
organize related data. Data can be stored on anything e.g. student records, employee records,
stock inventory records etc.

BASIC CONCEPTS
A database file is the entire database.

A table is an object in the database that contains related data on a specific topic e.g. personal
information on all employees in a department.

A record is group of related fields e.g. all the personal information of one person in the
department. In a table it is represented as a row in a table.

A field is a specific piece of data in a table e.g. the address of one person in the department. A
record is made up of multiple fields. In a table it is represented as a column in a table.

CREATING A DATABASE USING MS ACCESS

Steps:

1. Click on the MS Access shortcut or go to All programs, select MS Office and then click on
MS Access. This opens the Getting started screen.
2. To create a new database click blank database and in the panel on the right enter the
database filename [STUDENTS RECORDS]. Then click create.

(Have them close the database)

OPENING AN EXISTING DATABASE

Steps:

1. Click on the MS Access shortcut or go to ALL PROGRAMS, select MS Office and then click
on MS Access. This opens the GETTING STARTED screen.
2. If the database filename is listed in the panel in the right, open it or if not, select MORE
and locate the file where it is stored and open it.

(Have them open STUDENTS RECORDS)

N.B. If a security warning is displayed, click on OPTIONS and check the radio button that is
labeled ENABLE THIS CONTENT.

ACCESS NAVIGATION PANE

This is located on the left hand side of the MS Access window. It lists all of the objects – tables,
forms, queries and reports that have been created in this database. To open an object you
simply have to click on it.

CREATING A TABLE
By default when a database is created a table is created. To define the structure of the table and
attributes of the fields in that table, you must go to the DESIGN VIEW.

Steps:

1. Click on the icon labeled VIEW and select DESIGN VIEW. This will open a SAVE dialogue
box. Enter the name you will like the table to have: [FORM 1 STUDENTS]. This opens the
 table in design view and allows you to enter the field names required in this table and
specify their attributes - data type, description and properties. (Use the Log on to IT pg.
237 to give explanation of data types).

Field name Data type

StudID {PK} Number
Surname Text
Firstname Text
DOB Date/Time
Address Text
HPHone Text
Religion Lookup wizard
Sex Text

2. After entering the fields and their attributes, select a primary key. A primary key is a field
in the table that is chosen to uniquely identify a record. To do this select the field and
click the primary key icon. If the wrong field is selected it can be changed by selecting
the right field and clicking the PK icon.
3. After choosing the PK, save the structure of the table by clicking on the SAVE icon on the
quick access toolbar.
4. Change the view of the table to DATASHEET icon.

CREATING A TABLE FROM SCRATCH

Steps:

1. Click the CREATE tab and select TABLE DESIGN. This will open a table in design view and
allow you to enter the fields and their attributes.

Field name Data type

StudID Number
{PK}
Maths Number
Phys Number
Chem Number
Bio Number
Eng Number

2. Select the PK and click the SAVE icon. This will prompt you for a table name
[FORM 1 TEST]

ENTER DATA IN A TABLE

Data can be entered into a table in several ways: using datasheet view, using a form or using a
query.

Data Entry Using Datasheet View

Steps:

1. Ensure that the table is open in datasheet view.

2. Select the first field in the first record and enter the require data, move to the other
fields in that record using the arrow keys or TAB key and enter the data. N.B. if any field
is defined as AUTONUMBER, that field will be filled automatically as a record is added.
 3. After completing one record move to the next by pressing the ENTER key. The record is
automatically saved.

N.B. A primary key field must NEVER have a null value i.e. be blank. If it is left blank, an error
message is displayed. To continue a value must be entered in that field.

Form 1 Students

StudI Surname Firstname DOB Address HPhone Religion Sex

D
0001 Ali Fyzool 5/10/91 Penal
456- Hindu M
6789
0003 Barnes David 6/8/94 Mason Hall 639- Anglican M
4531
0047 Lara Leanna 23/3/91 Couva 677- Pentecostal F
5559
0032 King Mary 12/12/90 Fyzabad 987- Anglican F
5421
0209 George Gary 28/9/90 Canaan 655- Catholic M
0975
Creating a Form Using the Form Wizard

A form is a database object that provides a secure more aesthetically pleasing display for data
entry into a table. It allows you to add, update and delete records in a table from the form.

Steps:

1. Click the CREATE tab and select MORE forms from the forms group. From the menu
select FORM WIZARD.
2. From the first page of the form wizard select the table to be used as the data source:
[FORM 1 TEST].
3. Select the fields you wish to have displayed on the form, individually or all at once using
the > or >> buttons. Click NEXT.
4. Select a form layout of your choice. Click NEXT.
5. Select a style of your choice. Click NEXT.
6. Name the form [FORM 1 TEST].
7. Click FINISH.

Navigating records using a form

At the bottom left of the form are navigation buttons. To move to the next record click the right
arrow button. Click the >I button to move to the last record. To move backwards through the
record set use the left arrow button.

Create a New Record Using a Access form

To add a new record click on the navigation button with the star on the right of it, this should
bring up a blank record. Enter the given data. When finished close the form.

StudID Maths Phys Chem Bio Eng

0001 70 85 80 65 75

0003 25 40 55 45 65

0047 80 70 60 41 75

0032 75 65 76 65 80
 0209 60 65 75 65 88

Create a Query Using the Query Design Grid

Queries are used to select records from one or more tables based on specific criteria. There are
different types of queries:

 Select: this extracts data from a table or tables

 Upadate: this makes changes to existing records

 Append: this adds records to the end of tables

 Delete: this deletes records

 Make table: this creates a new table from specific fields

 Crosstab: this calculates and restructures data for easy analysis

Steps: {SELECT QUERY}

E.g. {List the name and date of birth of each female student in the class}

1. Select the CREATE tab, go to the OTHERS group and click QUERY DESIGN.

2. Select a query, table or the tables from which the data must be selected. To do this, right
click in the window and select SHOW TABLE. When that window opens highlight the
table needed [FORM 1 STUDENTS] and click ADD. Repeat by highlighting any other table
or query necessary and then click ADD. When all the tables have been added click
CLOSE.

3. Select fields [FIRSTNAME, SURNAME, DOB, SEX] from the tables by double clicking on
their names or in the QUERY DESIGN GRID, in each column click on the arrow and select
the appropriate field from the list.

4. Enter the query criteria [F] in the appropriate field in the row labeled CRITERIA.

5. Run the query by clicking on the RUN icon in the RESULTS group. This should display the
results of the query in datasheet view.

Practise the following:

1. List the names and sex of all Anglican students. Save as ANGLICAN

2. List the names and date of birth of each student born before 1993. N.B. date criteria
must be placed between # signs e.g. <#1/3/1997#

Selecting Data from Multiple Tables

To select data from multiple tables the tables need to be joined.

Steps:

1. Select the DATABASE TOOLS tab, then choose RELATIONSHIPS. The SHOW TABLE window
will appear.

2. Add all tables in the database, click CLOSE.

 3. Link the primary key [StudID] to the foreign key [StudID] by clicking and dragging the PK
to the FK. This opens the EDIT RELATIONSHIPS window.

4. Check ENFORCE REFERENTIAL INTEGRITY, then click create.

Practise the following:

 List all students and their results for each test. Save as GRADE SLIP

 List the students who scored more than 70 in their Maths test. Save as GreaterThan70

 List the students who scored less than 50 in their Biology test. LessThan50

Create a Report Using Report Wizard

Reports are used to organize and group information from tables/queries and format the data in
a way suitable for online viewing or for printing from the database.

Steps:

1. Select CREATE tab, then click the REPORT WIZARD.

2. Select the table/queries [GRADE SLIP] to be used as the DATA SOURCE for the report.

3. Select the fields required.[All]

4. Select the grouping level. [none]

5. Sort the records by a specific field [Surname Ascending]

6. Choose a layout

7. Choose a style

8. Name your report [GRADE SLIP]

9. Click finish