32
Lecture 4 - Introduction: What is a Relationship?
Definition in MS Access: An association between 2 common fields (column) in two tables.
There are three types of relationships:
1-One-to-One (1:1)
2-One-to-Many (1:N)
.(3-Many-to-Many (M:N
Why Define Relationships?
After you've set up different tables for each subject in your Microsoft Access Database, you
need a way of telling Microsoft Access how to bring that information back together again. The
first step in this process is to define relationships between your tables. After you've done that,
you can create queries, forms, and reports to display information from several tables at once.
For example, the form in Fig. 1 includes information from five tables
Fig. 1: A Form Using Information from Five Tables
33
How do relationships work?
In the previous example in Fig. 1, the fields in five tables must be coordinated so that they show
information about the same order. This coordination is accomplished with relationships
between tables. 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.
For example, employees can be associated with orders they're responsible for by creating a
relationship between the “Employees” table and the “Orders” table using the EmployeeID fields
(which we will show later)
1- One-to-One relationship:
In a one-to-one relationship, each record in Table A can have only one matching record in Table
B and each record in Table B can have only one matching record in Table A. This type of
relationship is NOT common, because most information related in this way would be in one
table. You might use a one-to-one relationship to divide a table with too many fields, to isolate
part of a table for security reasons, or to store information that applies only to a subset of the
main table. For example, you might want to create a table to track employees participating in a
fundraising soccer game.
2- 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. Refer to Fig. 4 for Supplier table as A, and
Products table as B
34
3- Many-to-many Relationship:
In a many-to-many relationship, a record in Table A can have many matching records in Table B,
and a record in Table B can have many matching records in Table A. This type of relationship is
only possible by defining a third table, called a junction table, whose primary key consists of two
fields: the primary keys from both Tables A and B. A many-to-many relationship is really two
one-to-many relationships with a third table. For example, the Orders table and the Products
table in Fig. 9 have a many-to-many relationship that's defined by creating two one-to-many
relationships with the Order Details table
35
Fig.3 : Junction Table in a Many-to-many Relationship in Northwind 2007.accdb
Defining a Many-to-many Relationship between Tables
Fig.3 shows a relationship view for Northwind database containing a many-to-many
relationship between Products and Orders. Please refer to it while you understand the following
steps .
1-Create the two tables that will have a many-to-many relationship
2-Create a third table, called a junction table, and add fields with the same definitions as
the primary key fields from each of the other two tables to this table. In the junction table, the
primary key fields function as foreign keys. You can add other fields to the junction table, just as
you can to any other table.
3-In the junction table, set the primary key to include the primary key fields from the other
two tables. For example, in an Order Details junction table, the primary key would be made up
of the OrderID and ProductID fields. (Note: You can set multiple fields as the primary key by
highlighting multiple rows (which correspond to fields) which you want to be part of your
primary key in Design View, and then on the Ribbon click on Design àPrimary Key).
4-Define a one-to-many relationship between each of the two primary tables and the
junction table.