Tables & Queries:
Design Tables

Step-by-Step: Design Tables

---

What you will learn:

to think through the steps to design and redesign of a table to use Spell Check to change field size to rename a table to copy and paste data between tables to use a foreign key to create a relationship between tables to view subdatasheets

---

Start with:  , Access open but no database open.

The story so far: 
World Travel Inc. wants to include in their database some information about various projects and which staff members are involved with which projects.

You will walk through the design process, and make some bad decisions along the way (not that YOU would do that in real life!).

Think First

What fields do you need to keep track of company projects and which staff members are assigned to each one? An ID code for the project, a project name, staff members' names, name of the Project Director. Does not sound too complicated, does it?

For one or two projects, most anything will do! To keep track of a large number of projects or several years of projects, however, means you need to be careful to not waste disk space and that your design will work for many different projects.

---

Create Table: Projects

You have to decide how many people might be working on a project. It's easy to find out how many there are right now. What about future projects?? Hmmm. We may run into a problem really quickly! For now, let's assume that there are at most 4 staff members plus a project director.

1. Create a new blank database and save it as projects.mdb to your Class disk in the folder databases project3.
   (Open Access. File | New... From the New File task pane, select New blank database)
    

2. In the Database Window, select Table and click on the Design button. The table Design View appears.
    

3. Complete the table with the following fields:
       ProjectID as an AutoNumber field. Make it the primary key by clicking the Primary Key button .
   Text fields:
       ProjectName, ProjectDirector, ProjectStaff1, ProjectStaff2, ProjectStaff3, ProjectStaff4
    

4. Save the table as Projects.
   

---

Edit Field Size

All of the text fields have the default size of 50 characters. That is too many for the name of the project

1. Click on the field ProjectName to select it.
    

2. In the General tab at the bottom of the window, change the Field Size to 30.
    

---

Add Records

1. Save the table as Projects.
    

2. Switch to Datasheet View and add records as shown below:
   

3. Spell Check: From the menu select  Tools | Spell Check  and make any needed corrections.
   The illustration has 1 error.
    

---

Thinking Again

Inspect the table so far. Do you see any problems or potential problems with this design?

Thought 1: Some fields are blank. That is wasted space.

Thought 2: Future projects might have more than 4 staff members assigned. You could just add another field, but it would be blank for many records.

Thought 3: The table only shows the job for the director. You cannot see if other staffers have particular job titles for the project.

Thought 4: There is no information about the project itself - like due date or goals or a description of what it is all about.

Changes Needed:

Split the table into a table of Project information and a table of ProjectStaff information. Create a relationship between the tables, which will allow you to see subdatasheets in the Project table. (A truly cool feature!)

---

Rename Table; Create New Table

To reduce wasted space and to provide more information and to allow for varying numbers of staffers, you will now create two tables. You can save yourself some typing by copying and pasting some of the data you already entered.

1. Close the Projects table.
    

2. In the Database Window, change the name of the Projects table to Projects-old.
    

3. Click the New button at the top of the Database Window. The New Table dialog appears.
    

4. Select Design View and click OK. The Table Design View appears.
    

5. Add fields for:
   ProjectID as AutoNumber and primary key
   ProjectName, with field size 30
   ProjectDescription as a memo field
    

6. Save the table as Projects and keep it open.

---

Copy and Paste Data Between Tables

You can copy and paste the names of the projects, if you do your selecting correctly.

1. Open the table Projects-old to Datasheet View.
    

2. Select the column ProjectName by clicking its heading.
    

3. On the keyboard, hold the CTRL key down and press the C button. The selected data is copied to the Windows Clipboard.
    

4. Switch to the new table, Projects, in Datasheet View.
    

5. Click the column heading ProjectName to select the column.
    

6. On the keyboard, hold the CTRL key down and press the V button. The data you just copied is now pasted into the table.
   A message appears warning you that you are about to paste 3 records.
    

7. Click OK. Your new table now has three records with names, but no descriptions.

   

8. Add the following to the ProjectDescription field:
    For record 1:  Special trips for volume or long-time customers to celebrate our anniversary.
    For record 2:  A database to give to our good customers in which they can record information about their trips. Goal date: June 15.
   For record 3:  Training seminar on computers with emphasis on agency software. For our own staff. Date: Dec. 15
    
   Copy & Paste: You can copy each description above and paste into its matching record.
    

9. Save the table and close it.

---

Create ProjectStaff Table with Foreign Key

Now you are ready to create a new table, ProjectStaff, for information about the staff. This table needs to show each staff member and what his/her job is on the project. (The ProjectStaff table does not need to have all of the contact information; that will be in another table.) The table also needs to include the primary key from the Project table. This field will be a foreign key in the new table. You need such a shared field to set up a relationship between two tables--in this case, to connect the project information with the staff members working on the project.

Having a separate table for staff information means you do not have to guess how many staff members will be working on a project.

1. Create a new table.
    

2. Add fields for:
   ProjectStaffID, as AutoNumber and primary key
   Text fields:
      StaffName, as Text, 50 characters
      Job, as Text 20 characters
      ProjectID, Number , in the Description section: Foreign key, from Projects table

   

3. Save the table as ProjectStaff.

---

Enter Data

The data from the original table is not laid out in a way that makes it easy to copy and paste it into the new ProjectStaff table. For these few records, it will be just as easy to type it all in. The old table did not have all of the job information anyway.

1. Switch to Datasheet View of the ProjectStaff table.
    

2. Enter the data as shown:
   
   (Your columns will not be quite as wide as in the illustration.)
    

3. Close the table.

---

Relationship

Next you need to create a relationship between the tables. The primary key of one table should be joined to its duplicate field in the other table. It is called a foreign key for that table.

1. From the menu select  Tools  and then  Relationships...
   A blank Relationships window opens.
    

2. If necessary, click the Show Table button . The Show Table dialog appears.
    

3. Click the table Projects to select it.
    

4. Click the Add button.
    

5. Repeat for the table ProjectStaff.
   Both tables appear in the Relationships window.
    

6. Close the dialog.
    

7. In the Relationships window, drag the field ProjectID from the list of fields in Projects and drop it onto the field ProjectID in the list of fields in ProjectStaff.
   The dialog Edit Relationships appears.
    

8. Click on the button Create. A join line appears that connects the two fields.

   
    

9. Click  on the Close button at the top right of the the Relationships window.
   A message appears asking if you want to save changes to the layout of Relationships.
    

10. Click on Yes.

---

View Datasheet and Subdatasheet

Now that you have defined a relationship between the tables, you can use subdatasheets to see who is assigned to which project. Such a useful feature!

1. Open the Projects table in Datasheet View.
   Each record has a + icon at the far left.
     Problem: No + icons at the left
           Why: You left the table open while creating the relationship.
           Solution: Close the table and open it again.
    

2. Click on the + for the first record, Anniversary Sales.
   A subdatasheet appears, showing you the records in the ProjectStaff table that are related to this Project.
    
   You can quickly view and even edit the related records for any project, directly from the table view. This works well when there are just a few fields in the subdatasheet. Not so well if there are too many to see at once in the window.
    
3. From the menu select  Format | Subdatasheets | Expand All.
   All subdatasheets are shown.
    
4. From the menu select  Format | Subdatasheets | Collapse All.
   All subdatasheets are collapsed again.
    

5. Close the table.

---

Thinking Some More

Using subdatasheets is quite handy but it does not take too many fields to make the subdatasheet too wide to see all of them at once. You need a better way to view and edit. That's what forms are for! Have some patience. You will learn about forms in the next project.

In the ProjectStaff table, it will be easy to make errors when entering a staff member's name. For example, William Gardner and Will Gardner and William P. Gardner are all the same person. Which way will you enter his name next week?? To make sure that names are the same throughout the database, you need a table for staff information that is linked to the ProjectStaff table. More and more tables!! That's for the next lesson.