Download Access 2007-Manual - Human Resources | Maricopa Community

RELATIONSHIPS
Related
Data
Access is a relational database management system. The database
consists of several subject-based tables. Data may be retrieved
from several tables when generating reports or requesting
information. Table relationships allow data in tables to be joined
to bring the information together as needed.
Linked Tables
After your tables are created they must be linked together. This is
established through a linking field or a common field between the
tables. Depending on how the tables are related will determine the
type of relationship required.
Referential
Integrity
When creating relationships, one table is typically the primary
table with the second being the related table. When the tables are
joined based on a common field between the two, Access ensures
the integrity of the data. For example, Order information (related
table) for a Customer (primary table) will not be allowed into the
database unless the customer exists. In other words, only
customers in your database are allowed to have orders. Trying to
find or reference a non-existing customer for an order would create
an orphan order. Referential integrity is used to prevent orphans
and keep references in sync so that the above situation never
occurs.
Access creates relationships based on the common field that was
used to join the tables. We will create relationships later.
Types of Relationships
Relationship
Description
One-to-one
The common data appears only once in both tables.
One-to-many
The common data appears once in the primary table, but many times in the
related table.
Many-to-many
The common data can appear many times in both tables. A third table must be
created to reduce the redundancy of the data and to create two one-to many
relationships.
33