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
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:
Project 1: Intro
Project 2: Access Basics
Project 3: Tables & Queries
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
database, the Students table has a
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!
Each student has many scores.
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
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!
Step-by-Step: Redesign Table
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
Projects database open.
Look for Duplicated Data
Now that you have a Staff table, you
need to check the design of your previous tables,
ProjectStaff to make sure that the tables will work well
(Tools | Relationships)
You established a relationship between the
Projects and ProjectStaff tables
earlier. You need to add the Staff table
to the window.
Click on the
Show Table button
. The Show Table dialog appears.
- Select the table Staff and click on the
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 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!)
field will join the ProjectStaff table to the
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
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
(Later you will create a query to make this
Lookup field behave even better.)
- In the Relationships window, right click on the ProjectStaff
the popup menu, select
The table opens directly into Table Design View. Slick!
- Add a new field named EmployeeID as a
Number type field.
the field EmployeeID and click on the
Lookup tab in the Field Properties.
- 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?
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
Bound Column: You want to save the
EmployeeID value from the
Staff table to the field with the same name in
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.
Switch to Datasheet view.
Select Values from Lookup
- Click in the EmployeeID field for
the first record and expand the Lookup list.
- Choose the record that matches the name in the
- Repeat for all records in the
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
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.
in the heading for the column StaffName to select the whole
- Right click on the column and from the popup menu select
A confirmation message box appears asking if you want to permanently
delete the data.
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
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
You can now create a relationship between the Staff and
- View the Relationships. (Tools | Relationships...) or
switch to the Relationships window if it is still open.
the EmployeeID field from the
ProjectStaff table and drop it on
the EmployeeID field in the
Staff table. The Edit Relationship
Access recognizes that the relationship is One-to-Many.
Your three tables are now joined in a sequence.
- Close the Relationships window.
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.
- In the Project table datasheet,
from the menu select click on the + icon
at the left of each record.
A subdatasheet expands to show the matching records from the
This is available only when there is a relationship between the tables.
- Close the ProjectStaff table.
- Open the Staff table and view
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
Idea: You could have made the
ProjectID field a Lookup field that showed
the name of the project instead of the ID number.
viewing method: From
the menu select | |
to view all of the subdatasheets at once.
- Close all tables.