Beginning MS Access 97

Unit 2: Designing a Database (Part I)

Description of Unit

This lesson introduces you to the process of designing your relational database and then applying your design to an Access database. The terms and definitions learned in the previous Unit will be used a great deal in this Unit. New terminology will be explained as it is used.

Objectives

Learn to sketch out your database prior to opening Access.
Learn to create a new Access 97 database.
Understand the use of multiple tables in your database.
Create multiple tables.

Designing Your Database

There are specific steps you should follow when designing your database:

Determine the purpose of your database. This will help you decide what data you want your Access database to store.
Determine the tables you will need. Once you have a clear purpose for your database, you can divide your information into separate subjects, such as "Employee" or "Orders". Each subject will be a table in your database. Remember your naming conventions from the last unit.
Determine the fields that you will need in your tables. Decide what specific pieces of information you want to store in each table. Each category of information in table is called a field and is displayed as a column in the table. For example, one field in an Employee table should be Last Name; another could be Hire Date.
Determine the relationships. Look at each table and decide how the data in one table is related to the data in other tables. Add fields to tables or create new tables to clarify the relationships, as necessary.
Refine your design. Analyze your design for errors. Create the tables and add a few records of sample data. See if you can get the results you want from your tables. Make adjustments to the design as needed.

Complete these steps using a piece of paper and a pencil or a whiteboard - anything erasable. You will probably revise these many times before you have a final design. Experiment with sample data and models of your forms and reports. Make sure the database design stores the data in the manner you need, retrieves the data correctly, and gives you the output (printed document) that you require. It is much more difficult to make changes to the tables, forms, and reports after real data is entered. Double-check your design to make sure it contains all the data you will require BEFORE entering the real data.

Common Design Problems

There are several common pitfalls to keep in mind as you design your database. These problems can cause your data to be harder to use, maintain, and retrieve. The following are signs that you should reevaluate your database design:

You have one table with a large number of fields that don't all relate to the same subject. For example, one table might contain fields pertaining to your customers as well as fields that contain sales information. Each table should contain data about only one subject.
You have fields that are intentionally left blank in many records because they aren't applicable to those records. This usually means that the fields belong in another table.
You have a large number of tables, many of which contain the same fields. For example, you have separate tables for January sales and February sales, or for local customers and remote customer, in which you store the same type of information. Try consolidating all the information pertaining to a single subject in one table. You may also need to add an extra field, for example, to identify the sales date.

Creating a Database

Before we can proceed with this Unit, an explanation of how to create a blank database needs to be given. Open your Access 97 program. Click the Start button - highlight Programs - find your reference to Microsoft Access and click on it. The Access 97 program will start.

Select Blank Database from the window that appears. Select the folder or directory that you want your database saved to and give it a filename. Click Create.

A database window will be displayed. You can also use the Access wizards to create a new database if you wish; however, for these units, we will not be using the database wizard. The Database Wizard creates all objects needed for your database as well as other objects that we will not be using in this workshop. It completely takes over the development of your database so using it in this instance would take the educational value out of these lessons. Also, please do not create any objects until you are given the instruction to do so.

Exercise 1: Create a new Blank Database now. Use the directions in the above paragraph to complete.

Creating Multiple Tables

Exercise 2: Create a table named tblStudents.

To create a table in a new database, click on the Tables tab in the database window. Click on the New button on the right side of the window. A "New Table" window is displayed. Select Table Wizard from this list and click OK. [NOTE: the wizard is a quick and easy way to create new objects.]

Select Students from Sample Tables list and then each of the following fields (highlight field and click the arrow that points to the right): StudentID, LastName, FirstName, Major, PhoneNumber, EmailName, and StudentNumber. Highlight StudentNumber and click the RenameField button at the bottom of the field list. Rename it to AdvisorNumber and click OK. Click the Next button to go on.

Rename this table to tblStudents and leave the "Yes, sets the primary key for me." from the center of the window. Click Next to proceed. StudentID will be the field that is unique on each record and let Access automatically number this field (is much easier). Click Next.

The final screen of this wizard allows you to begin entering data immediately, make design changes to the table, or enter data using a form that the wizard will automatically create. Select "Enter the data into the table using a form the wizard creates for me" and click Finish. Exit the form when it is displayed.

Click on the Form tab, highlight the form that was automatically created, right click on the form name, select Rename from the menu, and rename it frmStudent.

Navigating Tables

There are numerous ways to maneuver through an Access table. If you want to just look through the table without moving the cursor/highlight, use the scroll bar on the right side of the table. However, if you want to move the highlight as you move down the table, you can use the down arrow <Dn> or the page down <PgDn> keys. The same is true if you are moving up the table - use the up arrow <Up> or the page up <PgUp> keys.

If you want to move the highlight to the very first cell of the table (A1), press <Ctrl-Home>. Conversely, if you want to move the highlight to the very last cell of the table, press <Ctrl-End>. To move the highlight from field to field, use either the <Tab> or <Enter> keys.

Additional keystrokes & navigation tips will be discussed when first mentioned in the documentation.

Defining Relationships

Primary Key

Relationships between tables is a vital part of what makes Access 97 work so well. This one point makes a difference between having a bunch of data and having a relational database that helps you be more efficient and effective at your job.

Let's use an example to help clarify the relationship issue. We have information regarding both Students and Advisors in the College of Agriculture & Home Economics. There is related data in these pieces of information because each advisor has students to advise and every student has an advisor. Of course, this database could be one massive table that might contain all the necessary fields:

Student Last Name
Student First Name
Student Major
Student Phone
Student Email
Advisor Last Name
Advisor First Name
Advisor Building/Rm
Advisor Phone
Advisor Email

The difficulties of this approach are fairly obvious. Suppose one advisor works with 20 students. As information on each student is entered, the information for the advisor must also be entered. Entering the exact same information is tedious work to say the least. Remember: You should only enter any one piece of data ONCE. So, instead of entering the same advisor information for each of the 20 students, let's separate the data into two different tables with a relationship established.

Advisor Table
Last Name
First Name
Building/Rm
Phone
Email

Student Table
Last Name
First Name
Major
Phone
Email

So, how do we identify which students belong to which advisor? We would use a Primary Key. A Primary Key is a unique record identifier in each table. In our Advisor/Student database example above, there is no field that can be used as a Primary Key. Last name for either table is not a good choice - we could have duplicates for different individuals. First name in either table is not a possibility for the same reason - duplicates are possible. Address, phone, and email are not good choices because there can be duplicates but more importantly, they can change. Any field that can change is not a good choice for the Primary Key. The Primary Key should stay the same from the very beginning.

A standard, traditional database practice is to assign an ID number to each record. Access 97 provides us with AutoNumber. This is an integer that is incremented with each new record; the first record has an ID of 1, the next has an ID of 2, etc. Our ID field in each table becomes the Primary Key, the unique identifier for each record. This number stays the same regardless of how it is sorted or queried or printed. It is a static field.

Relationships

Now we need to look at how these two tables are related, how exactly advisors are related to students. Each student has one advisor but each advisor can have many students. This is called One-To-Many Relationship. Wow, something that makes sense!

The basic rule for establishing a One-To-Many Relationship is: make the table with the many point to the table with the one. In our example, Student is the table with many (remember, one advisor has many students) so we want to add a field to the Student table that points to the Advisor table. The new field we create needs to point to the Primary Key (ID) of the Advisor table. The Advisor ID field in the Student table, since it refers to the Primary Key of another table, is known as a Foreign Key. The Foreign Key must be the same field type (Number) as the Primary Key.

The easiest way to set a primary key is to let Access set it for you in the Table Wizard.

Summary

In this unit you have created a new database and two new tables. You've learned that the object wizards (i.e., Table Wizard) are helpful in developing objects for your database. You've also learned the foundation for understanding relationships between tables. We will use this information in a later lesson so make sure it is clear to you.

The next unit will continue using the database & tables we created in this Unit, so make sure you save your database before exiting (File - Save).

A table name can be up to 64 characters including letters, numbers, and spaces. Creating meaningful names for all objects [remembering the naming convention from the previous chapter] will help to make it easier to use and manage.

Additional Exercises

Define Primary Key.
Define Foreign Key.
Define a "relationship" between two or more tables.
Review the process of creating a new database & table. Practice navigating around a table using the mouse & Access buttons as well as the directional keys.

Answers to questions 1-3.

Project Question

Determine, on paper, the tables you will need for your database. Your purpose was defined in the previous Unit - divide your information into subjects. Each subject will be a table in your database.

Project Task

Create a new database and name it "first name initial + last name" (i.e., cireland). Do NOT use the database wizard to complete this task.


Top of Page	LGTA HomePage