Access Basics:
Relationships

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 starwars.mdb.

Relationships

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.

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.

Avoids typing 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!

The more records you have and the more information that would be duplicated in each one, the more attractive a relational database becomes.

Types of Relationships

There are three kinds of relationships between tables.

One-to-Many

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 one supplier.
 

Many-to-Many

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.
 

One-to-One

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 purpose.

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.  ~~

Last updated: 07 Oct 2011