Tables & Queries:
Redesign Table

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

After you add another table to a database, you need to look for duplications. Did your table design include data that is already in another table? You can remove duplicated fields. Less data entry for you!

Using a Lookup field lets you choose data instead of having to type it in. This reduces the opportunities for mistakes and saves wear and tear on your fingers, too.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing Tables Arrow: subtopic open
    Icon: StepDesign Tables
    Icon: StepFormat Fields
    Icon: StepIndexes
    Icon: StepValidation
    Icon: StepRedesign Table
    Icon: StepDocument a Table
    Designing QueriesTo subtopics
    ExercisesTo subtopics

Project 4: Forms & Reports



Creating a One-to-One or a One-to-Many relationship between tables allows you to view subdatasheets inside a table's datasheet view. For example, in the LanguageArtsClasses database, the Students table has a One-to-Many relationship to the Scores table. So in the Students table you can open a subdatasheet that shows all of the student's scores. Subdatasheets can be nested up to 8 levels deep!

Example: Table with subdatasheet

Each student has many scores.

Table Properties - Subdatasheet Name - list droppedOnly one kind of subdatasheet can be seen, even if the table has relationships with several other tables. You can choose which subdatasheet shows in the Table Properties dialog. (Table Design View and click the Properties button) In the property Subdatasheet Name you can choose from a list of all the tables and queries in the database. Some choices will result in a blank subdatasheet because there is no relationship. The default choice is [Auto].
The Students table has a One-to-One relationship to the table 6 Weeks Averages. Each student has one set of averages for the grading term. Changing the Subdatasheet Name in the Properties dialog to the table 6 Weeks Averages makes the subdatasheet show only one record, the averages. This could be useful!

Datasheet with new subdatasheet

Icon: Step-by-Step 

Step-by-Step: Redesign Table

 Icon: Step-by-Step

What you will learn:

to add a table to the Relationships window
to change field to a Lookup field
to select values using a Lookup field
to delete a field from datasheet view
to create a relationship between tables
to view subdatasheets

Start with:  Class disk, Projects database open.

Look for Duplicated Data

Now that you have a Staff table, you need to check the design of your previous tables, Projects and ProjectStaff to make sure that the tables will work well together.

  1. View the Relationships.
    (Tools | Relationships)
    You established a relationship between the Projects and ProjectStaff tables earlier. You need to add the Staff table to the window.
  2. Relationships: Projects, ProjectStaff, StaffClick on the Show Table button Button: Show Table. The Show Table dialog appears.
  3. Select the table Staff and click on the Add button.
    The Staff table appears in the Relationships window. Access cannot automatically join the Staff table to the other two. There are no fields in common but there is duplicated data.

    Problem - No relationship for Staff table, yet there is duplicated data:
    None of the fields in the Staff table appears in the other two tables. The StaffName field in the ProjectStaff table repeats the data in the fields FirstName, MiddleName, LastName in the Staff table. The Staff table is a better place to keep an employee's name info, along with all the other info about the employee.

    Solution: Replace the StaffName field with a new field, EmployeeID . (Don't change anything just yet. You need to make some other changes first!)

    The EmployeeID field will join the ProjectStaff table to the Staff table.

    Benefit: Using one source for the names will keep the ProjectStaff table from having different versions of a person's name in different project records.

Change Field to Lookup

You will add the EmployeeID field to the ProjectStaff table and make it a Lookup field that pulls its values from the Staff table. Once you select the matching values for each record, you will be able to delete the StaffName field.

(Later you will create a query to make this Lookup field behave even better.)

  1. In the Relationships window, right click on the ProjectStaff table.
  2. Right Click Menu: Table DesignFrom the popup menu, select Table Design.
    The table opens directly into Table Design View. Slick!
  3. Add a new field named EmployeeID as a Number type field.
  4. Table Design View: Project Staff - EmployeeID - Lookup propertiesSelect the field EmployeeID and click on the Lookup tab in the Field Properties.
  5. Change the values to match the following:
    Display Control = Combo Box
    Row Source Type = Table/Query
    Row Source = Staff
    Bound Column = 1
    Column Count = 6
    Column Heads = No
    Column width = 0";0";0";1";1";1"
    List Rows = 8
    List width = 3"
    Limit to List = Yes

    Why these values?
    Combo Box:
    This creates a drop list that can show the first, middle, and last name of each employee so that you can choose one for the record in the ProjectStaff table.

    Bound Column: You want to save the EmployeeID value from the Staff table to the field with the same name in the ProjectStaff table. Since EmployeeID is the first field in the Staff table, you set the Bound Column to 1. No matter what fields are shown in the combo box, only the value in the Bound Column is actually saved.

    Column Count: This is the number of fields from the Staff table that are part of the combo box. The number must include any fields that are to the left of the ones that you actually want to see. In the datasheet view of the Staff table, the fields show in the order: EmployeeID, SocialSecurityNumber, NationalEmplyNumber, FirstName, MiddleName, LastName.  So the Column Count must be 6.

    Column Widths: You can hide a column that you don't really want to see by setting its width to 0". So using  0";0";0";1";1";1" means that the first three columns are hidden. Each column from Column Count must have a width set.

    List Width: The drop list will be as wide as the column in the datasheet unless you set it to be wider here. To see all three name columns, you must make this width at least as wide as the sum of the column widths.

  6. Icon: Save Save the table.
  7. Icon: Datasheet Switch to Datasheet view.

Select Values from Lookup

  1. Click in the EmployeeID field for the first record and expand the Lookup list.
  2. Choose the record that matches the name in the StaffName field.
     ProjectStaff table, using Lookup field
  3. Repeat for all  records in the ProjectStaff table.

    In the EmployeeID field you now see the first name for the record that matches the number that is actually stored in this field. That is because the FirstName field is the first column that was displayed in the combo box.
    Confusing?? Yes, especially if two people have the same first name! You are not done with this table yet!
    Later you will create a query that will let you show a full name instead of just the first name. Another approach would be to change the order of the fields so that the LastName was displayed. But, you could still have two employees with the same last name, so that change would not fix everything.

Delete Field (in Datasheet View)

You do not need the StaffName field anymore. The EmployeeID field will join the tables. You can remove the field from the Datasheet view.

Why delete the field? Removing the field will avoid a problem with data inconsistency. For each project the staff names might wind up being entered differently if you are typing them in directly. That will make it hard or impossible to answer simple questions like "What projects is Luis Perez assigned to?" because the sort and search features will not think that the different spellings are the same person.

Remember that our goal is to enter data once. So a person's name should be entered in one table and then pulled from that table for other uses.

  1. ProjectStaff  table with StaffName selected - popup menu = Delete ColumnClick in the heading for the column StaffName to select the whole column.
  2. Right click on the column and from the popup menu select Delete Column.
    A confirmation message box appears asking if you want to permanently delete the data.

     Message: Do you want to permanently delete the selected field(s) and all the data in the field(s)?

  3. ProjectStaff table after deleting field StaffNameClick on Yes.
    The field is deleted.

    This table is now more efficient but it is harder to read without the complete name of the staff member. (It would be even worse if we were looking at the numbers in the EmployeeID field instead of first names!)
    "Good" table design often makes the table harder to work with directly but it is worth it to keep data errors as low as possible.
    Later you will use queries and forms to access the records in a more user-friendly way.

Create Relationship

You can now create a relationship between the Staff and ProjectStaff tables.

  1. View the Relationships. (Tools | Relationships...) or switch to the Relationships window if it is still open.
  2. Dialog: Edit Relationships - Staff, ProjectStaffDrag the EmployeeID field from the ProjectStaff table and drop it on the EmployeeID field in the Staff table. The Edit Relationship dialog appears.
    Access recognizes that the relationship is One-to-Many.
  3. Relationships: Projects, ProjectStaff, StaffClick on OK.
    Your three tables are now joined in a sequence.
  4. Close the Relationships window.

View Subdatasheets

Your tables can show more information than is actually in the table. It's not particularly convenient, but at least it is there! Access will automatically create subdatasheets on the One side where tables have a One-to-One or a One-to Many relationship. If a table is related to several tables, Access may have guessed wrong as to which subdatasheet you wanted to see. You can change this behavior in the Table Properties dialog in the Subdatasheet Name property.

  1. In the Project table datasheet, from the menu select click on the + icon Icon: Expand at the left of each record.
    A subdatasheet expands to show the matching records from the ProjectStaff table. This is available only when there is a relationship between the tables. 

    Table Datasheet View: Projects with Subdatasheet

  2. Close the ProjectStaff table.
  3. Open the Staff table and view the subdatasheets.

    Since you joined the ProjectStaff and Staff tables with the EmployeeID field, the Staff table can show to which projects each staff member is assigned. Now if we remembered what project went with those ProjectID numbers, it would all make more sense!
    Idea: You could have made the ProjectID field a Lookup field that showed the name of the project instead of the ID number.

    Staff table with subdatasheet from ProjectStaff.

    Menu: Format | Subdatasheet | Expand AllAlternate viewing method: From the menu select  Format | Subdatasheet |  Expand All  to view all of the subdatasheets at once.

  4. Close all tables.