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 Database from the opening dialog box
you will now be presented with a Save Dialog Box
- save the file as <Student List>
- note the file type for Access is Microsoft Access Databases or <.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.

Update in Progress. No changes have been made past this point. Do not continue.

Defining the Fields

The Access Database Window. Note the title bar at the top reads Student List : Database (Access 2000 file format).
Tables is currently selected in the Objects Pane

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 Table Dialog Box

The first field, Name, has been entered. As you enter fields in a table, watch the “Help Box” (blue print) on the right in the Field Properties section of the window. The information here will guide you as you enter fields.

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
- note that the default value, once entered, is now enclosed in quotes
type student city 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 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

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 (see pictures below); 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
  - clickto 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

Lookup Wizard step one
Lookup Wizard step two
Lookup Wizard step three
	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!

review the discussion of a “prompt” (see link in the box above) and then set a “prompt” for the Zip Code field
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
  - review the discussion of a “prompt” (see link in the box above) and then set a “prompt” for the Teacher Name field
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

If you like, you can close the Table you just created. Do not close the database!

Saving the Table, Defining the Primary Key

choose FileSave 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

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 buttonon the toolbar or select Edit Primary Key or right-mouse click anywhere in the table 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; this will open the table in Datasheet view (you were working in Design view when you entered the field names)
- 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.

Creating a Student List Database

Start Microsoft Access

Defining the Fields

Saving the Table, Defining the Primary Key

Entering Data in Tables

Go Back