Relationships work by matching data in key fields. The best way to ensure two fields will relate to one another are to make sure the related fields have the same name, data type and length 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.
There are three basic types of relationships.
A relationship between two tables where both the primary key and the foreign key are unique in considered “one-to-one.” For each record in the first table, there will be one and only one record in the second table.
In a one-to-one relationship, each record in the primary table can have only one matching record in the foreign table and each record in the foreign table can have only one matching record in the primary table.
A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
A relationship between two tables where the primary key is unique but the foreign key allows duplicate values is considered “one-to-many.” For each record in the primary key table there can be multiple records in the foreign key table.
A one-to-many relationship is the most common type of relationship. In a one-to-many relationship, a record in the primary table can have many matching records in the foreign table, but a record in the foreign table has only one matching record in the primary table.
A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
A relationship between two tables when many records in one table can relate to many records in another table is considered “many-to-many.” Many-to-Many relationships cannot be created in Access.
Figure 8. Many-to-Many Relationship (Using a Junction Table)
In the above example, with a many-to-many relationship, a record in the Employees table can have many matching records in the Project table, and a record in the Projects table can have many matching records in the Employees.
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 the Employees and Projects Tables.
A many-to-many relationship is really two one-to-many relationships with a third table.