an Access Database:
A Primer for Resource Center Managers
by
Laura C. Larsson
Building Relationships (Between Tables)
Relationships Table of Contents
IntroductionSample Entity Relationship Diagram
Types of Relationships
One to Many
One to One
Many to Many
Defining Relationships Between Tables
What are the Possible Relationships?
Entity Relationship Diagrams for Serials Database
For More Information
Introduction
Once you have built tables, you need some method of bring the information in the tables back together. You need a relationship between the tables and those relationships need to be defined. Once you get the relationships built, you can create queries, and forms and reports. from several tables at once.Building relationships is frustrating even if you understand the theory of building entity relationship diagrams. (We will not be covering the theory for this workshop). For those who have not been trained in computer science, it can sometimes be a nightmare. As mentioned elsewhere, I spend a good deal of my time gnashing my teeth, muttering under my breath, and even crying tears of frustration as I try to figure out what the relationships are. (They are as difficult as human relations and about as complex).
Microsoft Help says that a "relationship works by matching data in key fields, usually a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table". Below you will see a sample entity relationship diagram from the Students and Classes database and below that a description of the three types of relationships: one to many, one to one, and many to many.
Sample Entity Relationship Diagram
Below you will see an ilustration of an Entity Relationship Diagram from the Students and Classes database which is one of several databases available from the program CD.
Look a large scale representation of this diagram.
Types of Relationships
You can create several types of relationships between tables:- one to many
- one to one
- many to many
One to Many Relationship
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A. For example, one publisher can publish many journals, but it's highly unlikely that a journal would have more than one publisher. Simularly, one bindery could receive many volumes each year to be bound, but each volume would only go to one bindery.One to One Relationship
In a one-to-one relationship, each record in one table can have only one matching record in a second table, and each record in the second table can have only one matching record in the first table. This type of relationship is fairly uncommon, because most information related in this way would be in one table. We do not have any examples of a one to one relationship in our serials database.Many to Many Relationship
In a many-to-many relationship, a record in one table (table 1) can have many matching records in a second table (table 2), and a record in table 2 can have many matching records in table 1. This type of relationship is only possible by defining a third table (called a junction table) whose primary key consists of two fields - the foreign keys from both Tables 1 and 2. A many-to-many relationship is really two one-to-many relationships with a third table.We have one example of a many to many relationship in our serials database.
Defining Relationships Between Tables
It's easier to define a relationship once you have determined what that relationship is by adding the tables you want to relate to the Relationships window, and then dragging the key field from one table and dropping it on the key field in the other table.Before you begin creating relationships, close all tables and switch
to the Database window. From the Menu bar, select the Relationships 
icon.
Since you haven't yet defined a relationship, the Add Tables/Queries box will automatically be displayed. Add the tables you want to create relationships between (all of them) by selecting each and clicking on Add.
Put the Logical Journal table in the center of the page since that will be the most heavily linked table.
Drag the field that you want to relate from one table to the related field in the other table. This will likely be the primary key field (which is displayed in bold text). You will drag it from one table to a similar field (often with the same name) called the foreign key in the other table.
You will also want to check the Enforce Referential Integrity
check box otherwise you can't see the one-to-many relationships very well.
Access will put a 1 and an infinity sign 
(horizontal
8) to indicate the many relationship at the appropriate end of the link.
With the Enforce Referential Integrity box checked, records in a related
table are deleted if their primary table entry is deleted. This keeps you
from having "hanging" bits and pieces of a database.
You will recall that we created 18 tables. We will need to create relationships
for each of these tables. The (main) LogicalJournal table will have several
relationships.
What are the Possible Relationships?
Before you begin, let's think about possible relationships. If you can't think of the answer, select the question number to be taken to an explanation.Q. 1. What is the relationship between
the publisher and the logical journal?
Q. 2. What is the relationship between
the journal and the Kardex (issues received)?
Q. 3. What is the relationship between
the journal and its Websites?
Q. 4.
What is the relationship between the journal and its editors?
Q. 5. What is the relationship between
the journal and the vendor/agent?
Q. 6. What is the relationship between
the journal and its routing lists?
Q. 7. What is the relationship between
the journal and the issues being bound?
Q. 8. What is the relationship between
the volume being bound and the company doing the binding (the bindery)?
Q. 9. What is the relationship between
the bindery and payment?
Q. 10. What is the relationship between
the bindery and the publisher?
Q. 11. What is the relationship between
a journal and its supplement?
Q. 12. What is the relationship between
the journal and a claim for a missing issue?
Q. 13. What is the relationship between
the journal and the ordering or renewal of journal titles?
Q. 14. What is the relationship between
the journal and the Web?
Q. 15. What is the relationship between
the journal and its subject headings?
Entity Relationship Diagrams for Serials Database
Look at the Entity Relationship Diagram for the Serials database after you've tried to create them yourself.The first time I tried to create an entity relationship diagram for
another database, I ended up crying tears of rage because I just felt so
stupid. The relationships just didn't make any sense and I couldn't figure
out how many foreign keys to put into each table. The answer is, of course,
that you only include a foreign key if you're going to use it. Any other
foreign keys should be deleted.
For More Information
Look at About relationships in a database, Define a many-to-many relationship between tables, Display the Relationships window, and What is referential integrity? in Microsoft Help.Next: Creating Forms
Back: Creating Tables