E

B A S L ES
A
B

T A SE
T

DA ATA
B
A

T
F EATIN G
D

O
S CR
R O
IC
M
 WHAT IS A DATABASE?

A database is a collection of
information that is organized so
that it can easily be accessed,
managed, and updated.
By:
http://searchsqlserver.techtarget.
com/definition/database
CREATING A DATABASE USING
MICROSOFT ACCESS
Click on the Microsoft Access Icon (This may be found by clicking on the task bar,
desktop
or
by clicking on START then ALL PROGRAMS followed by Microsoft Office)
CREATING A DATABASE USING
MICROSOFT ACCESS
Click on the BLANK DATABASE
CREATING A DATABASE USING
MICROSOFT ACCESS
Click on the Folder to right hand side of File name
This folder allows you to browse to the location you want to
save the file or database you want to create
CREATING A DATABASE USING
MICROSOFT ACCESS
Type the Name of the Database. (This database should be called
Kingston Library)
Click OK
CREATING A DATABASE USING
MICROSOFT ACCESS
Click Create
CREATING A DATABASE USING
MICROSOFT ACCESS

You have just created your first Database

 CREATING A DATABASE

For any information to be store

and organized in a database it
MUST be placed in tables.
TABLE STRUCTURE
 CREATING A DATABASE

Now Let us create two (2) TABLES

using the fields from your Text
Book , Access Made Simple for
the individual tables:
1.Member
2.Catalog
 CREATING TABLES

Click on create
 CREATING TABLES

Click on Table Design

 CREATING TABLES

This screen should come up. This is the

DESIGN VIEW of your TABLE
 CREATING TABLES

Type in the field names (column headings)

 CREATING TABLES

A Data type specifies what form of data /

information can be store in the different fields.
 CREATING TABLES

Choose the most appropriate data types

for Each of your fields
DESCRIPTION

Each field can have a

description other than the
field name or caption. The
description can be used to
provide information about
the field.
 ADDITIONAL FIELD PROPERTIES

FIELD SIZE
This is the maximum number of
characters that can be entered in a
field.

For a text field, the default number is 255

characters.
 ADDITIONAL FIELD PROPERTIES
FORMAT
The format property can be used to create custom
formats that change the way numbers, dates, times
and text display

It does not change the way the data is stored, only how
it is displayed

To change the format of a field different symbols are

entered in the Format text box.
Text and memo data types any of the four symbols as
shown in the table below.
Symbol Description Example

@ A required text @@@-@@@@ would display 2436789

character or space as 243-6789. Nine spaces or
characters are required
> Forces all > Would display SMITH whether you
characters to entered SMITH, smith, Smith
uppercase
< Forces all > Would display smith whether you
characters to entered SMITH, smith, Smith
lowercase
& An optional text @@-@@& would display 12345 as 12-
character 345. and 12.34 as 12-34. Four out of
five characters are required and a fifth
is optional
 ADDITIONAL FIELD PROPERTIES
INPUT MASK
An Input mask is a pattern that controls
the data that can be entered in a field.

It consist of literal characters, which are

displayed in the field, and mask
characters, which are not
Literal parenthesis surrounding the area
code of a telephone number.
 ADDITIONAL FIELD PROPERTIES

Mask characters are symbols that control

where the data is entered in the field,
the type of data that can be entered,
whether the data required or optional,
and the number of characters
CHARACTERISTICS OF INPUT MASK
Charact What character is accepted/its Entry required
er function
0 Digit only yes
9 Digit or space no
# Digit, space,+, - no
L Letter yes
? Letter Optional
A Letter or digit Yes
A Letter or digit Optional
& Any character or space Yes
C Any character or space Optional
< Converts characters to lower case
> Converts characters to upper case
! Displays from left to right
\ Displays the next character
 ADDITIONAL FIELD PROPERTIES
CAPTION
The caption is the label that is displayed on a
form. If you do not type in a caption, the default
label will be the name of the field.

Enter captions in initial caps as this has a

tendency to present less eye strain than all
caps
 ADDITIONAL FIELD PROPERTIES
DEFAULT VALUE
The default value is used to specify a
value that is automatically entered in a
field when a new record is created
The property is commonly used when
most or all of the entries in a field will
be the same
Character default values are enclosed by
quotation marks (‘’ ’’) and date default
values by number sign (#)
EXAMPLES OF DEFAULT VALUES
Value What is stored in the field
Date() The computer system’s date
500 500
“Barbados” Barbados
#01/21/2006 01/21/2006
 ADDITIONAL FIELD PROPERTIES

VALIDATION RULE
When entering data, a check known as validation check
can be performed on the data to ensure that the
data is valid.

This check is only performed if a validation rule was

entered in the table structure
 ADDITIONAL FIELD PROPERTIES

VALIDATION RULE
The validity is check by entering an expression
containing the acceptable value.

Expressions may made up of numeric, text or date

data, relational operators/logical operators
VALIDATION RULE
Number
The data of the number data type must not be
enclosed in quotations marks

Text
Should be enclosed in quotation marks

Date/Time
The data of the Date/Time field must be enclosed
by # signs
VALIDATION TEXT
When you add a validation rule, you can also add validation
text in the validation text property box

The error message that is displayed when the validation rule

is broken is validation check.

You may create your own error message to notify the user of
the problem.

Validation text appears in a message box if you attempt to

enter invalid information in a text field for which there is
validity check
For example a field to only enter numbers 1 to
10, you might create validation text that
would display the message “The only
entries for this field is numbers 1 to 10.”

If you do not specify a message, a default

error message will be displayed
Field Data Expression Result
Type
Salary Number >2000 Accepts only salaries that
are more than $2000
Gender Text “M” or “F” Accepts M or F entries
only
Birth Date/ >=#1/13/1989# Accepts birth dates that
Date Time AND are greater than or equal
<=#12/31/2000# to 1/13/1989 and less than
or equal to 12/31/2000

Result Text “PASS: or “FAIL” Accepts only entries with

pass or fail
REQUIRED PROPERTY
This property could set to Yes or No.

When it is set to Yes, it is necessary for the user to

input data in the field. This feature is very useful
for ensuring that data is entered in certain fields
that are relevant for processing the data

For example, in a payroll system, in order to

calculate the gross pay, entering the pay rate is
necessary
ALLOW ZERO LENGTH

Setting this property to Yes allows a zero-length

string to be entered in the field.

You may only use this option in Text, Memo or Hyperlink

data types
INDEXED PROPERTY
An indexed property will arrange
records to facilitate fast searches of
the database.

Selecting ‘Yes-no duplicates’ prohibits

duplicate records for this field

Selecting ‘Yes-duplicates’ allows

duplicates.
 HOW TO MODIFY YOUR TABLE DESIGN

After you have created your table,

you need to go back to table design
to make changes:

Right-click on its name in the list of

tables
Click Design View
 HOW TO MODIFY YOUR TABLE DESIGN

INSERTING FIELDS
If you are inserting a field at the bottom of
the list, you just click inside the first
available row and start typing

If you want to insert a field between existing

Table:

Right- click on its name in the list of tables.

Click Design View
 HOW TO MODIFY YOUR TABLE DESIGN

DELETING FIELDS

To delete a field:

Right – click in the row and click

Delete

Click Yes when the confirmation box

pops up.
 HOW TO MODIFY YOUR TABLE DESIGN

You have to be careful when you are

changing certain field properties-
particularly if the table already contains
data

For example, you need to be careful when

changing the field size and the data
type, because you run the risk of losing
data
 CREATING A DATABASE

Now Create the remaining One

(1) TABLE
 CREATING A DATABASE

Two or more tables can share

information or records by
linking common fields in each
table together. This is called a
relationship
 CREATING A DATABASE

For the tables to be link together

they must have a common KEY.
 WHAT ARE KEYS

There are different types of keys

in a Database structure. These
are as follows:
 KEYS

Primary Key – this is a field that

uniquely identifies each record
in a table. It is the field that
creates the relationship for two
or more tables. E.g. TRN
 KEYS

Candidate Key
This is a field that can become
the Primary Key of a given
table.
 KEYS

Secondary / Alternative Key

This is a candidate key that has
not been selected as the
primary key.
 KEYS

Composite Key
This is a primary key that consists
of two or more fields together.
 KEYS

Foreign Key
This is a field in a table that
creates the link with the
primary key field in another
table.
 SETTING KEYS
In order to set the primary key of a
table:
Open the table’s design

Select the field(s)

Right- click on the selected field(s),

then click Primary Key
 CREATING THE RELATIONSHIP

Determine which field in each table will take part

in the relationship. Make sure that one of the
fields is the primary key.

Normally you set the relationship between the

primary key in one table and its foreign key in
the other table
 CREATING THE RELATIONSHIP

Click the Database Tools tab of the Ribbon, then click

Relationships

Add the tables involved in the relationship

Drag from one field to the other in order to set the

relationship

Enforce referential integrity (optional but

recommended)
 DELETING A RELATIONSHIP

Click on the black line that

represents the relationship

Press the Delete key

Click Yes at the confirmation

prompt