Table Relationships in Access
In this blog I have described use of relationships between
tables in Access Database. The main objective to create relationships between
tables, to remove data redundancy (duplicate data). To achieve that goal, you
divide your data into many subject-based tables so that each fact is
represented only once. You then provide Access with a way to bring the divided
information back together — you do this by placing common fields in tables that
are related. To do this step correctly, though, you have to understand the
relationships between your tables, and then specify these relationships in your
Types of table relationships
There are three types of table relationships.
one-to-one relationship, a row in table A can have no more than one matching
row in table B, and vice versa. A one-to-one relationship is created if both of
the related columns are primary keys or have unique constraints.
one-to-many relationship is the most common type of relationship. In this type
of relationship, a row in table A can have many matching rows in table B, but a
row in table B can have only one matching row in table A. For example, the
Publishers and Titles tables have a one-to-many relationship: each publisher
produces many titles, but each title comes from only one publisher.
one-to-many relationship is created if only one of the related columns is a
primary key or has a unique constraint.
the primary key side of a one-to-many relationship is denoted by a key symbol.
The foreign key side of a relationship is denoted by an infinity symbol.
many-to-many relationship, a row in table A can have many matching rows in
table B, and vice versa. You create such a relationship by defining a third
table, called a junction table, whose primary key consists of the foreign keys
from both table A and table B. For example, the Authors table and the Titles
table have a many-to-many relationship that is defined by a one-to-many
relationship from each of these tables to the TitleAuthors table. The primary
key of the TitleAuthors table is the combination of the au_id column (the
authors table’s primary key) and the title_id column (the Titles table’s