Creating a Student List Database
The Scenario: You are a elementary school
teacher in your home school system and you wish to keep track of some basic
student information. So let's create a database!
Start Microsoft Access
- launch Microsoft Access
- select Blank Access Database from the
opening dialog box
- click <OK>
- you will now be presented with a Save Dialog Box
- save the file as <Student List>
- note the filename extension for Access is <.mdb>
Notice that with Access you save the file
before you start working with it! This is common with most database
programs.
|
 |
 |
The next step after starting an Access
database is to define the fields to be used in your database. This is
done by creating a TABLE. |
Defining the Fields
|
|
- this is the next dialog box you will see
- with Create table in Design view selected, click on the Design
button, or simply double-click on Create table in Design view
- this will open a Table, which for now is named simply Table1
(we will save it later), which we will use to define the fields
- the first column, Field Name, is
used to enter the names of each field
- the second column, Data Type, is
used to select the field type
- the third column, which is optional, is used to enter
descriptive information about each field
|
- the first field will be called Name, it will be used to store the
name of each student
- type Name in the field name column
- press <TAB> to move to the next column
- the field type Text should appear,
press <TAB> to accept this field type
- type student name in the description
column
- press <TAB> to move to the next line
- the second field will be called Address, it will be used to store
the street address of each student
- type Address in the field name column
- press <TAB> to move to the next column
- the field type Text should appear,
press <TAB> to accept this field type
- type student street address in the
description column
- press <TAB> to move to the next line
- the third field will be called City, it will be used to store the
city where each student lives
- type City in the field name column
- press <TAB> to move to the next column
- the field type Text should appear
- enter Georgetown as the default value
in the section on the lower left of the table dialog box under the
General Tab
- click in the description column to accept this field type with a
default value
- type student city in the description
column
- press <TAB> to move to the next line
- the fourth field will be called State, it will be used to store
the state where each student lives
- type State in the field name column
- press <TAB> to move to the next column
- the field type Text should appear
- enter KY as the default value
- click in the description column to accept this field type with a
default value
- type student state in the description
column
- press <TAB> to move to the next line
since
it is reasonable to assume that every student attending a local elementary
school very likely lives in the same city, and certainly in the same state,
why do we need to store this information in our database?
- the fifth field will be called Zip Code, it will be used to store
the postal zip code where each student lives
- type Zip in the field name column
- press <TAB> to move to the next column
- the field type Text should appear
- change it to the Lookup Wizard
field type
- the Lookup Wizard Dialog Box will open; you will now
create a list of the zip codes
- select “I will type in the values that I want”, then click
- in the next box that appears, accept the number of columns
as 1, press <TAB>
- enter Select Zip Code, press <TAB>
- this will serve as a “prompt”, see important
comments below in red
- enter 40324, press <TAB>
- enter 40325, press <TAB>
- enter 40326, press
- in the next box that appears, enter Zip
Code for the label for the lookup column
- click <FINISH> to close the Lookup Wizard Dialog Box
- type student postal zip code in the
description column
- press <TAB> to move to the next line
- note that the field name is now Zip Code
since that is what you entered as the label for the lookup column
|
|
Why
did we do this? Whenever all of the information is known in advance, in
this case you certainly know the zip codes for the area served by your
school, it makes data entry easier if the choices are presented to the
user. This ensures that the correct items are entered and in the format
you prefer. We will do this again for the Teacher's Name field. For the
teachers, you know the names so you present the list to the person doing
the data entry and s/he simply selects from the list.
Pre-defined
lists often include as the first choice what is commonly referred to as a
“prompt”. What is meant by a “prompt”? This is really important so
click here to read more about it!
|
- the sixth field will be called Parents, it will be used to store
the name(s) of the student's parents
- type Parents in the field name column
- press <TAB> to move to the next column
- the field type Text should appear,
press <TAB> to accept this field type
- type name of student's parents in the
description column
- press <TAB> to move to the next line
- the seventh field will be called Phone (H), it will be used to
store the home phone number
- type Phone (H) in the field name column
- press <TAB> to move to the next column
- the field type Text should appear,
press <TAB> to accept this field type
- type home phone number in the description
column
- press <TAB> to move to the next line
- the eighth field will be called Phone (W), it will be used to
store the home phone number
- type Phone (W) in the field name column
- press <TAB> to move to the next column
- the field type Text should appear,
press <TAB> to accept this field type
- type work phone number in the description
column
- press <TAB> to move to the next line
- the ninth field will be called Room Parent, it will be used to
store whether or not the mother or father is willing to volunteer to help
in the classroom
- type Room Parent in the field name column
- press <TAB> to move to the next column
- the field type Text should appear
- change it to the Yes/No field type
- click on the “down” arrow to open the “drop-down”
list of fields
- select the Yes/No type
- under the Lookup tab at the bottom left of the table
screen, the Display Control should read Check
Box
- click in the description column to accept this field type and
display control
- type volunteer activity in the description
column
- press <TAB> to move to the next line
- the tenth, eleventh, and twelfth fields will be, respectively, Read to
Class, Provide Snacks, and Organize Field Trips; each
represents an additional parental volunteer activity
- each will be a Yes/No field type with a
Display Control of Check Box
- the description of each will be volunteer
activity
- create each of these fields using the steps outlined for the field Room
Parent
- the thirteenth field will be Teacher, it will be used to store the
teacher's names
- type Teacher in the field name column
- press <TAB> to move to the next column
- the field type Text should appear
- change it to the Lookup Wizard
field type
- the Lookup Wizard Dialog Box will open; you will now
create a list of the teacher's names
- select “I will type in the values that I want”, then click
- in the next box that appears, accept the number of columns
as 1, press <TAB>
- enter Mary Jones, press <TAB>
- enter John Singer, press <TAB>
- enter Jim Smith, press <TAB>
- enter Nancy Wilson, press
- in the next box that appears, enter Teacher's Name
for the label for the lookup column
- click <FINISH> to close the Lookup Wizard Dialog Box
- note the field type now reads Text
- set the default value, in the Properties box below, to Select Teacher Name
- this will serve as a “prompt”, just like with
the Zip Code field
- type teacher's names in the description
column
- press <TAB> to move to the next line
- the fourteenth and last field will be DOB, it will be used to
store the student's birth date
- type DOB in the field name column
- press <TAB> to move to the next column
- the field type Text should appear,
change it to Date/Time
- type student birth date in the description
column
Saving the Table, Defining the Primary Key
- choose File
Save
or press Ctrl + S
- enter Field Names as the name for the table,
click
- Access will now warn you that you have not defined a Primary Key. Since
we will not be setting up a relational database, you need not have a
Primary Key. Click
- close this table by clicking on the close box
If you decided later that you
needed a primary key, you would do the following:
- open the table
- switch to Design View
- select the field name to be the Primary Key
- click anywhere in the row of the field to be the primary or select
the entire row
- click on the Primary Key button
on the toolbar or select Edit
Primary Key or right-mouse click and select Primary Key from the
pop-up contextual menu
You will see a key in the gray box to the left of the field row for any
field that is set as a Primary Key. To remove a Primary Key, simply follow
the same steps to set the key.
Entering Data in Tables
If you wanted to enter data into your database you could do so via the
table we just created as follows:
- you will now see an item called Field Names
in the Student List : Database window
- double click on it to open the table
- you can now enter data into your database via this table
- type the name of the first student in the Name
column
- press <TAB> to move to the next column, Address
- type the address, press <TAB> to move to the next
column, City
- note the default entry
- accept this name by pressing <TAB>
- or enter a new name then press <TAB>
- continue entering data and pressing <TAB> to move to
the next column
- for the volunteer activity columns simply check the box if the
parent will do the volunteer for that activity, leave it unchecked if
not
- for the teacher's name, click on the down arrow to open the list,
select the name of the teacher
- when you press <TAB> at the end, you will start a new
row
|
However, forms are a more user-friendly way to view
and enter data in a database!
We will now
create a form and then enter the data. |
Copyright © 2003
last updated August 12, 2003