A single table in a database is a
lonely and weak thing. Several related tables can be
much more efficient and powerful.
By creating multiple tables and establishing
relationships between the tables, you can drastically increase the
usefulness of the database. It makes it easier to store more information and to
answer more complex questions.
Examples below are from
Tables are related when they share a field. It reduces confusion
to use the same name in both tables.
In the illustration below,
there are four tables. The shared fields are EpisodeID, CategoryID,
and SupplierID, which are the
primary key fields in their own tables. These are called
foreign keys for the Star Wars Collectibles table.
Where you are:
Project 1: Intro
Project 2: Access Basics
Project 3: Tables & Queries
Project 4: Forms & Reports
Why Use Related Tables?
Even with just a few related tables, you can save yourself a lot of typing and a lot of confusion.
Saves repeating data: Without a table Suppliers, each record in the main table,
Star Wars Collectibles, would include all of the supplier's information. So
much extra typing! With a related Suppliers table, you only need to enter the
supplier's ID number in the record.
errors/inconsistencies: Different versions of a value will sort and filter
differently. Can you get it right every time? "1 - The Phantom Menace 1999" is not the same as "The
Phantom Menace" or, when your fingers get uncooperative, "Teh Phantom Memace"!
With a related Episodes table all of that is decided once. Then you just have
to get the episode's ID number into the field in the main table. The easiest
way is a Lookup list.
Lookup lists: A Lookup field lets you select
information from a list. This guarantees that there are no typing errors and
that you use the same values throughout the database!
records you have and the more information that would be duplicated in each one, the more
attractive a relational database becomes.
There are three kinds of relationships between tables.
The most common type of relationship.
A record in the first table can match many records in the second table, but a
record in the second table matches only one record in the first table.
One Episode can match many items in Star Wars Collectibles but
each item is from a particular episode. (The table includes "not in movies"
for Star Wars items that are not actually in a movie episode.)
One Supplier can match several items but each item came from only
This kind of relationship requires creating a third table to
join the records in the other two.
Example: In a classroom database, each assignment is done by many
students. Each student has many assignments. The Assignments and
Students tables can be related
through a third table, Scores. Each record in the new table Scores is matched
with one assignment and one student.
Not common. Each record in the first table matches only one
record in the related table and each record in the second table matches only
one record in the first table.
Why not just put all of the information into one table?
When there are many, many fields, splitting a table can
make working with the records easier.
You might split the table to put some information in a
table that has a password, for security.
You could use a duplicate or partial duplicate table for a special event or
Example: You create a table for each club or sports team in a school. Each member is a student.
So the Football Players table is identical to the Students table, or has many
of the same fields, but has an additional field to indicate if the student is
on the football team.
~~ 1 Cor. 10:31 ...whatever you do, do
it all for the glory of God. ~~