Access Basics:
Relationships

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


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.

Relationships in the Star Wars Collectibles database


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics Arrow: subtopic open
    InterfaceTo subtopics
    Getting StartedTo subtopics  
    Access ObjectsTo subtopics
    Relationships Arrow: subtopic open
    Icon: StepTable Wizard
    Icon: StepLookup Wizard
    Icon: StepTable Relationships
    Summary
    Quiz
    ExercisesTo subtopics

Project 3: Tables & Queries

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



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.


LessonsWorking with Databases Previous Page Next Page




Teachers: Request permission to use this site with your class
 
Copyright © 1997-2012 Jan Smith   <jegs1@jegsworks.com>
All Rights Reserved

Icon: DonwloadIcon: CDWant a local copy with no ads? - Download/CD

Want to help?


~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~


Last updated: 30 Apr 2012