Contents

Contents
Contents......................................................................................................................... 1
How to set up a database in Microsoft Access.............................................................. 1
Creating a new database................................................................................................ 3
Enter field names and select data types......................................................................... 4
Format date fields: how do you want fields with date data type displayed?................. 5
Format currency fields: How do you want fields with currency data type displayed? . 6
To set up a primary key................................................................................................. 7
Changing the length of a text field in Microsoft Access............................................... 8
Validation Rules: Pull-down lists (file lookup)............................................................. 9
Validation Rules: Picture (Format) Checks ................................................................ 11
Validation Rules: Presence Checks............................................................................. 12
Validation Rules: Range Checks................................................................................. 13
Validation Rules: Uniqueness Checks ........................................................................ 14
Entering data into a database....................................................................................... 15
How to create a form in Microsoft Access.................................................................. 17
How to set up a database in Microsoft Access
Create a New Database
↓
Enter field names and select the data types
↓
Format fields with date/time and currency data types
↓
Set up a primary key
↓
Chose field lengths for all the text fields
↓
Set up validation rules
↓
Enter the data into your database
1
2
Creating a new database
Open up Microsoft Access.
Go to the File Menu and select
New.
Then click on Blank Database.
Give the database a sensible name, when
asked, and then click on Create
The database manager
window will now
appear (shown in the
picture on the left).
Click on “Create table
in Design View” and
you will be able to
start setting up your
database.
3
Enter field names and select data types
Put the name of the field in
this column and then select
the desired data type from
the pull down list.
The data type text lets you enter any mixture
of characters (letters, numbers and special
characters like [email protected]:;,-)
The data type number only allows numeric
data. If you go to format you can select
integer (whole numbers only) or decimal
(sometimes called real numbers – numbers
with a decimal point in them)
The data type yes/no is what Access calls
Boolean fields – there are only two possible
values Yes and No.
You can also select data types like dates, times
and currency (money) if these are needed in
your database.
4
Format date fields: how do you want fields with date
data type displayed?
You can chose how you would like a date to
be shown e.g. 1st June 2006, or 01/06/2006 or
01-Jun-06.
To do this select the field which has a
date/time data type and then go to Format and
select your preferred option from the pulldown list.
In the example shown above the Date of Birth
is being set up to have the Short Date Format.
5
Format currency fields: How do you want fields with
currency data type displayed?
You can chose how you would like a currency
field to be shown e.g. £1000, or $1750 or
£1000.00.
To do this select the field which has a currency
data type and then go to Format and select
your preferred option from the pull-down list.
In the example
shown left the
Hourly Wage is
being set up to
be in £s.
You can then chose how
many decimal places you
want shown (select 2 if you
want the pennies shown, 0 if
you want to have the value in
pounds only)
6
To set up a primary key
A primary key is a field that is unique for
every record.
Select the field that you
want to make the primary
key and then click on the
key icon.
If you want to set up a composite primary key
(make several fields together be the primary
key because there is not a field that is unique
on its own) select all the fields that will be part
of the composite key and then click on the key
icon. Note: to select several fields hold down
the ctrl button on the keyboard while you use
the mouse to select all the fields.
If you do not set up a primary key then Access
sometimes creates one for you and calls it ID.
7
Changing the length of a text field in Microsoft Access
Select the field and then in field size type in
the length (maximum number of characters
you want to allow the user to type in to the
field).
In this example the surname field has had its
length (field size) set to 30:
8
Validation Rules: Pull-down lists (file lookup)
In this example we are going to look at how to
set up a pull-down list.
In a database about students we want to make
sure that only male and female can be typed
into the gender field.
Select the gender field and make
the data type text. Then change
the data type to lookup wizard.
Select “I will type
in the values I
want” and then
click on Next.
If you make a
mistake at any time click on Back to go back
to the last screen.
9
Type in the items that
will be in the list, as
shown in this picture
and then click on
finish.
Then click on the Lookup
tab and select Limit to
List. Type in Yes. If you
do not do this last bit then
there will be a pull-down
list that the user can select
from but they could also
type in whatever they like.
10
Validation Rules: Picture (Format) Checks
Sometimes the data in a field must have a certain pattern e.g.
National Insurance Numbers and Postcodes.
In this example we are going to set up a format check on
National Insurance. All NI Numbers start with 2 letters then
have 6 numbers and finally a letter.
Select the NI Number field
and then in input mask type:
LL 00 00 00 L
An L means that something
must be a letter, a 0 (zero) that
something must be a number.
The next screenshot shows a slightly more complex input
mask that has been set up for a postcode, where the data
entered must match one of four possible formats.
It is also possible to use input masks to make sure that the
data entered has upper or lower case letters, or characters
other than letters & numbers in certain positions (e.g. you
could use an input mask to ensure that an e-mail address has
an @ symbol in it).
11
Validation Rules: Presence Checks
In this example we are going to look at how to
set up a presence check. A presence check
makes sure that data is entered in a field (it
cannot be left blank).
In a database about students we want to make
sure that every student has a name.
Select the name field.
Then where it says Required type in:
Yes
Where is says Allow Zero Length
type in:
No
12
Validation Rules: Range Checks
In this example we are going to look at how to
set up a range check. For a field called Mark
on Test we want to make sure that only
numbers between 0 and 100 are entered.
First of all make the data type
Number
Then where it says Validation
Rule type in:
>=0 and <=100
(This means must be greater than
or equal to 0 and less than or
equal to 100).
Note: To make a field be greater than 0, you would have typed in >0 where it says
Validation Rule.
13
Validation Rules: Uniqueness Checks
MS Access automatically makes a field you
set-up as a primary key have a uniqueness
check.
If there are any other fields that you want to
set up a uniqueness check for then select the
field and then:
Change the indexed
property to say “Yes
(No Duplicates)”.
14
Entering data into a database
When you have finished setting up the
database click on this icon:
You will be asked to save the
table, give it a sensible name.
Your screen will now look like this:
You can now type in the data. When you have
finished typing in the data click on the cross to
close the table.
15
The database manager will now look like the
picture above. Double click on the table name
if you want to add more data to your database.
If you want to change the setup of the database
then select the table name and then click on
design.
Instead of typing the data into the table
directly you could create a form and use the
form to enter the data. A form is a more userfriendly way of entering data into a database.
16
How to create a form in Microsoft Access
Click on Forms tab on
the database window.
Select Create Form by
Using Wizard.
Click on the double
arrow to move all the
fields from available
fields to selected fields.
Then click on Next.
Select the layout you
would like – here
justified has been
selected – then click
next.
17
Then select the style
you would like your
form to be in – here
standard has been
selected – and then
click on next. Finally,
give your form a name and click on finish.
The finished form is shown below – you can
now use this form to type in more information
to your database.
The arrows at the bottom of the form can be used to
move between records and to add a new record. All the
data that you enter using your form will automatically be
added to your table.
Clicking on this icon (in the top-left
corner) will take you to the design view
of the form where you can move fields around,
change colours, insert pictures etc... Example:
to change font colour right click on the field
and select properties.
18