Beginning MS Access 97

Unit 3: Designing a Database (Part II)

Description of Unit

This unit is a continuation of Unit 2 and will include creating a table from scratch, creating a form, entering data into a table and using a form, and creating a relationship between two tables. You will use the database that you created in the previous unit. Please open Access 97 and the database now.

Objectives

• Learn to create a table from scratch.
• Learn to a create a form.
• Become familiar with entering data into a table and a form.
• Create a logical relationship between tblStudent and tblAdvisor.
• Set Primary Keys & Foreign Key in tables.

Creating a Table from Scratch

In order to gain much more control over the development of a database table, you can create it from scratch using Design View. In Design View, you enter your own field names, descriptions, and choose your own data type to associate with each field. You also set your own Primary Key.

Before creating a table entirely from scratch, you should write down your basic table structure on paper, focusing particularly on field names and data types. Access fields can have one of the following data types:

While it will not be necessary for you to know all of these data types it is a good idea to have a general knowledge of the possibilities in defining the data fields.

Exercise 1: Create a new table from scratch.

Click on the Tables tab in the Database Window. Click on the New button; a New Table dialog box opens. Click on the Design View option and click OK. A blank table will open in Design View.

Type ID in the first Field Name column, press <Tab> to go to the Data Type column. [Text automatically appears in the Data Type field as soon as you press the <Tab> key but you can select from any of the 11 options on the list.] Click the down arrow to the right of the field & select AutoNumber from the list. <Tab> to the Description column & type in a brief description of this field (i.e., Advisor Last Name, Advisor Office Phone).

Repeat the steps in the above paragraph for each of the following field names, all are Text data types: LastName, FirstName, Address, PhoneNumber, and EmailName. When you are finished, your screen should look similar to this picture.

Exercise 2: Set a Primary Key

Click on the ID field. [Remember, Primary Key is a field that is unique in each record of the table.] An arrow will appear in the field selector column (far left). Click the Primary Key button on the menu bar. [See screen capture to identify exact location.] The arrow in the field selector column will turn into a key symbol. This is a toggle field so if you need to remove the primary key from this field, select the primary key field and click the Primary Key button on the toolbar again.

Exercise 3: Set Format Properties

Access 97 always sets each field with the default format for its data type. This format defines how the field displays in tables, forms, and reports. You may want to change this format to one of the other options. For example, a date/time data type can be changed to display long (Tuesday, July 7, 1998) or short (7/7/98).

In Design View, add a new field (Date) to the existing table. The Data Type should be Date/Time. Make sure this field name is selected (the arrow should be displayed in the field selector column). The General tab should be displayed below. Click on the Format field. A down arrow is displayed at the right edge of that field, click on the arrow. A list of possible formats is displayed. Select the format of your choosing. A diagram might help.

Exercise 4: Set Field Size Properties for Text Fields

The default field size for a text field is 50 characters. You can change this size to any amount from 0 to 255 characters.

Click the field selector column of the field name you wish to change. Click the General tab in the Field Properties. Enter the new field size in the Field Size text box. A picture is worth 1,000 words!

Save your table [File-Save] as tblAdvisors.

 

Entering Data

There are a couple of notes of interest before you begin the next group of exercises. Telephone number fields will include the area code parentheses when using the wizard - not a huge deal.

Exercise 5: Enter Advisor Data

Enter the following information directly into the tblAdvisor previously created. Notice that you can use the <Tab> or <Enter> keys to move from field to field. Use the arrow keys to move around the table fields as well.

Joe Smith, 423 Hulbert, 335-1111, joe_smith@xwsu.edu
Jane Zane, 211 Food Science, 335-7777, jane_zane@xwsu.edu
Bryan Brown, 333 Clark, 335-6666, bryan_brown@xwsu.edu

Access will create an ID number automatically for each of these records as you create them. Do NOT type a number in the ID field, it contains the words (AutoNumber). After you have entered the above data, close the table. Save and close the table.

Exercise 6: Enter Student Data

Now enter the following data using the form, frmStudent. Find the Advisor number from the table above and enter that, not the advisor's name, in the Advisor ID field.

Shawna Davis, 335-7676, Ag Econ, sdavis@stu.wsu.edu, Joe Smith (advisor)
Jerry Turner, 333-2222, Food Science, jturner@stu.wsu.edu, Bryan Brown (advisor)
Sherry Vineyard, 333-1111, Natural Resources, vineyard@stu.wsu.edu, Jane Zane (advisor)
Deke Einstein, 335-7799, Ag Econ, einstein@stu.wsu.edu, Joe Smith (advisor)
Jay Terry, 333-0000, Natural Resources, jterry@stu.wsu.edu, Jane Zane (advisor)

Save and close the table.

 

Creating a Relationship

In order to create the relationship between the two tables in our example, you need to identify the primary key and the foreign key.

Exercise 7: Set the Foreign Key

The Primary Key concept was discussed in detail in Unit 2 (review it if necessary). You have allowed Access to set your Primary Key (Unit 2: Exercise 2) and you have set the Primary Key on your own (Unit 3: Exercise 2).

The Foreign Key in the tblStudent table must be the same data type (Number) as the Primary Key in the tblAdvisor because that is the link between the two tables. To check this, from the database window, highlight tblAdvisor and click the Design button.

The ID field is highlighted. The data type is "AutoNumber" and the Field Size is "long integer". Find those two fields. Close this window.

Highlight the tblStudents from the database window and click the Design button. Highlight the AdvisorNumber field. Click the Data Type in the AdvisorNumber field and a pull-down arrow appears. Click on that arrow and select "number" from the list; "long integer" should appear in the Field Size field automatically. This step MUST be completed before progressing to Exercise 8.

In some database programs, this is sufficient - defining the Primary and Foreign Keys. But with Access, we have to specifically define the relation between these tables.

Exercise 8: Create a relationship between tblStudent and tblAdvisor

From the Tools menu, select Relationship. A "Show Table" window is displayed. Add the tblAdvisor and tblStudent to the Relationships screen and close the window. Two lists are displayed - each containing the field names in each of the tables.

To actually join the two tables, use the mouse and click the ID field in the Advisory table (our Primary Key). While holding down the mouse button, move the cursor over to the Advisory Number field (our Foreign Key) in the Student table list.

A "Relationships" window is displayed (NOTE: If you click on the "Tables aren't related" option, you can undo a table relationship you previously made.)You'll notice that under Relationship Type that One-To-Many is already selected. Also, check the Enforce Referential Integrity to insure that data entered in the Student Advisor ID field can not be a value that does not exist in the Advisor table. Click the Create button.

After the relationship is created, the Relationships screen shows a connection between the Primary Key and Foreign Key fields. Notice that the Advisor table has a 1 while the Student table has an infinity sign. Together, this signifies the One-To-Many relationship.

Save and Close. Exit the database.

Now that the relationships are defined, it is easy to begin creating queries, forms, and reports.

Summary

In this unit, you have created a new table from scratch and a new form. You have also created the link between your two tables that make your database a "relational database". You are gaining marketable skills every minute you work through this course! You should feel confident in your skills with these tasks. If you are not, please return to the beginning of this unit, re-read the entire document, and complete the exercises again.

Additional Exercises

1. I think that is enough work for one week - whew!

Project Question

Determine the fields for each of your tables. Decide the specific pieces of information that you will store in each table. Each category of information is a field. Refer to Unit 2 if you require further information.

Project Task

Create the tables you determined as necessary in the Unit 2: Project Question in your Project database. Use the fields you determined necessary in the above Project Question.

© 1998 - May not modify or copy without the consent of the authors.

Top of Page	LGTA HomePage