Relational Databases- The Address Book

Back

Introduction:

All the Access databases we have created thus far have been flat databases. In a flat database each record can only contain one line of information for each field. A simple example of a flat database is the address book we designed in skills 1. One problem that occurs in this project is that you can only enter one phone number or email address for each person when we know more than one is very likely.

In this project you will re-design the address book using a relational database format. Any person in your address book will be able to have as many phone numbers or email addresses as you like; a significant improvement to the skills 1 project.

Procedure:

Getting started...

We will begin by creating the Address book from skills 1 but we will make some slight modifications. There are 3 important changes:

  1. We will need to build more than one table.
  2. An autonumber field as a primary key will be very important.
  3. Making relationships between tables will be new and necessary to make a relational database.

Step 1

Step 2

Addresses Table

Step 3

 

*It is very important that there is no primary key and no autonumber datatype in this table!*

Multiple Entries Table

Step 4 (NEW SKILL!)

We will now build what is called a relationship. There are many aspects to relationships but to keep things simple for this project the directions will build you a 1 to many relationship between the 2 ID fields in the 2 tables.

Follow the instructions below to create a 1 to many relationship:

A) Select <Tools> <Relationships>
B) Add both tables; Addresses and Multiple entries
C) Close the "Show Table" window.
D) Select the ID field in the Addresses table and click/drag to the ID field in the Multiple entries table.
E) Select the 3 check boxes, "Enforce Referential Integrity" and the 2 below it.
F) Select <Create> and save the relationship.
G) Close the relationships window.

**If you have followed all the steps above correctly your relationships should look like this:**

1 to Many Relationship

Step 5 Add 1 record

Open the Addresses table and add the following entry:

** If this is done correctly you should see a small "+" sign next to this record.**
If you select this "+" sign you will see that the Multiple entries table appear.
Add the following information:

Be sure to enter multiple entries not all in the same cell... VERY IMPORTANT!

Notice...
When you select the "+" sign, the ID field is not listed in the Multiple entries table. This is due to the relationship that was created. If you open the Multiple entries table separately you will see that the ID field has been given the value from the Addresses table. It links the 2 fields together.

Step 6 Add more required data

Add the following:

Step 7 Add your own data

Add 8 more entries. This data can be real or faked. This information may be seen by many students so only include information you are willing to share. 3 things to consider when adding data:

A) Make sure the data you add is realistic.
B) Include multiple phone numbers, email addresses and comments in your multiple entries table
C) Model a re-world situation. The data should vary as it would in a real address book.

Step 8 Make 3 queries

Query #1- Phone-Email

Use First, Last, Phone and Email. Sort by last name. Include all records.

Query #2 Address

Use First, Last, Address, City, State, Zip, and Comment. Return only the teachers in your database (I know there are 3 for sure!)
We will change the criteria so we can return different results from your query.

Query #3 Phone-Comment

Use First Last Phone and Comment. Filter for something in your Comment field. Your choice.

Step 9 Make 3 reports

Make reports for each query.

The reports need to be a professional product. Use grouping levels to help achieve a professional look.

We will discuss details about what these reports should look like.

Step 10 Create peel off mailing labels

a. Build a query that contains first, last, address, city, state and zip
b. Add logical sorting
c. Go to reports and select "new" in the toolbar . One option is "label wizard" .
d. Work thru the wizard and build mailing labels that could be printed off and placed on an envelope!
e. To truly do this you would go to the store and purchase the labels and it would have a product code you would use in the wizard.
f. For this project, pick any product code you like. Just remember, if you do this for real , the product code you select must match the labels you purchase at the store.

Step 11 Make it user friendly- Building forms and subforms

Use the form wizard to build a user-friendly form . We will also build a subform so all the data entry can be done from one screen. To place a subform on the main form will require opening up the "tool box" and using the wizard. We will discuss this is class.

To achieve the proper look and feel will require some trial and error.

Step 12 Using the "tool box" to build buttons

In the "tool box" you can select a button (just like in Excel) and create all the options you want the user to have on this form. The options are endless!!
a. Create buttons that will open all 3 of your reports . Make sure to choose "Preview Report "
b. Create a link to your mailing labels .
c. Create a button that will open the "find record" feature as well.
d. Add 2 more buttons of your choice that you think are interesting. (You should have 7 buttons total when you finish)

Step 13 Create a "Mail Merge" in Word

Mail Merge is used to create documents that pull information from the database. One Mail Merge letter can produce as many documents as there are records in your database. Very powerful.
a. In your database create query that will contain all fields except number and comment. Add logical sorting.
b. Open Word and go to <tools> and Mail Merge . Follow the directions in the wizard and link the Word document to your Mail Merge query in your address book database.
c. This will take some practice, but try to create the same Word doc I did. I will be showing you in class my results.

Grading Criteria

Select this link to see the Grading Requirements for this project.