Microsoft Access 2000
Microsoft Access 2000 is a powerful database application. This tutorial
shows how it is used to manage a simple set of data.
Getting Started
A Few Terms
These words are used often in Access so you will want to become
familiar with them before using the program and this tutorial.
A database is a program for storing data and information.
An object is a competition in the database such as a table, query,
form, or macro.
A table is a grouping of related data organised in fields (columns)
and records (rows) on a datasheet. By using a common field in two
tables, the data can be combined. Many tables can be stored in a
single database.
A field is a column on a datasheet and defines a data type for a set
of values in a table. For a mailing list table might include fields for
first name, last name, address, city, state, zip code, and telephone
number.
A record in a row on a datasheet and is a set of values defined by
fields. In a mailing list table, each record would contain the data for
one person as specified by the intersecting fields.
Design View provides the tools for creating fields in a table.
Datasheet View allows you to update, edit, and delete in formation
from a table.
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Starting Access
To Start Access you have to double-click on the Access 2000 icon from
the Novell Applications Window.
MS Access 2000
After opening Access, you will be presented with the window shown
below. Select Blank Access Database from this window;
Blank Database
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Blank Access database
Unlike Word documents, Excel worksheets, and Power Point
presentations, you must save an Access database before you start
working on it.
After selecting "Blank Access database", you will first be prompted
to specify a location and name for the database.
On the save in menu select your home drive, which the drive with
your user number on it
For the file name type Sample1 and click OK
This will take you to the database window, which is shown below. This is
the starting point for creating new, and accessing existing, Tables,
Queries, Forms and Reports.
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Exercise 1 – Creating Tables
Access stores its data in tables. A table contains multiple RECORDS
(rows). Each record contains several FIELDS (columns)
We will be creating a Table in Design View so double click on
Create table in Design View
This will take you to the Table design view Window which is shown
below;
When designing a table you must decide what fields (e.g. name,
address, etc.) should appear in each record.
Field Names
The Field Properties Window contains columns for the Field Name, Data
Type and Description for each field.
To enter a field’s properties
Click in the Field Name box Enter Surname
Click in the Data Type column
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Data Type is text by default. By selecting the button in the Data type
box this can be changed to one of the following;
Click in the Field Size which is under the field properties heading box
and change the value to 18 in the field size.
Now enter three other field names as follows;
Field Name: Address Data Type Text, Size 30
Field Name: Area, Data Type: Text, Size: 12
Field Name: Distance, Data Type: Number, Size: Single
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Primary Keys
One field in the table must be unique to prevent duplication of records.
Do this by assigning a primary key. Here the field SURNAME is chosen
(for simplicity)
To create a Primary Key
Click on the SURNAME field.
Click the Primary Key button on the toolbar at the top.
Saving the Table
Access insists that you name and save the table before entering data
into it.
To Save the table
Choose File, Save, type Addresses
Click OK
Entering Data into the Table
On the toolbar, click the DATASHEET VIEW button
Place the pointer between Address and Area in the Column
headings
Click and hold down the mouse’s left button
Widen the ADDRESS column to allow for addresses of around 30
characters
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Now Enter the data shown below
Addresses
Surname Address Area Distance
Bury 7 Fylde Mansions Thurlton 4.2
Cartwright 27 Martinet Drive Cobbold 4.8
Dickinson 46 Compton Place Cobbold 4.2
Emsworth 30 Windrop Avenue Cobbold 4.9
Geele 17 Sale Road Arnold 3.1
Green 39 Inkerman Street Arnold 2.9
Jackson 37 Inkerman Street Arnold 2.9
Jones 23 Wandleys Lane Cobbold 4.1
Smithson 121 Stocks Road Arnold 2.5
Thompson 85 Inkerman Street Arnold 2.8
Wickes The Force, Booth Hall Road Thurlton 7.1
Saving the Data
Data is automatically saved every time you press the Enter key
Printing the Table
Click the Print button , or go to File and click on Print and then the
OK button to print the table.
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Exercise 2 - Creating a Query
Access uses Queries to sort or pick out data from the table
Queries select records from one or more tables in a database so they
can be viewed, analyzed, and sorted in a single table, which is saved
and can therefore be easily used in the future.
To create an Overall query
From the menu click Window, click Database
In The Database Window, Select Queries,
Double-click Create Query in Design View
Select ADDRESSES from the Show Table dialog box
Click Add then click Close
Double Click on the Surname and address fieldname from the
ADDRESSES box and they will appear into the columns as shown
below:
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Click the Datasheet View button or the Run Query button
on the toolbar or the. The query should show all Surnames and
Addresses in the table as shown below
Query1
Surname Address
Bury 7 Fylde Mansions
Cartwright 27 Martinet Drive
Dickinson 46 Compton Place
Emsworth 30 Windrop Avenue
Geele 17 Sale Road
Green 39 Inkerman Street
Jackson 37 Inkerman Street
Jones 23 Wandleys Lane
Smithson 121 Stocks Road
Thompson 85 Inkerman Street
Wickes The Force, Booth Hall Road
Save this Query as Query1 by going to File and clicking Save
Print out this Query
Searching for particular records
To seek out records from our table you can enter a search word in the
criteria cell of the Query Design View
To find all the people who live in Cobbold
Click the Design View button on the toolbar
Select ADDRESSES from the Show Table dialog box
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000
Double Click on the Surname, Address, Area and Distance
fieldnames from the ADDRESSES box and they will appear into the
columns
Click the Criteria cell of the Area column
Type Cobbold and press the Enter key
The Area column should now look like this;
Click the Datasheet View button or the Run Query
button on the toolbar
You should see only the records for the people who live in the Area
Cobbold as shown below;
Query1
Surname Address Area Distance
Cartwright 27 Martinet Drive Cobbold 4.8
Dickinson 46 Compton Place Cobbold 4.2
Emsworth 30 Windrop Avenue Cobbold 4.9
Jones 23 Wandleys Lane Cobbold 4.1
Print out this Query
Learning Waves / Learning Express softwareSKILLS 2002 /2003 Access 2000