Microsoft Access Relationships is referring to the how data in one table is related to data in other tables. Tables can be related to another table(s) in three different ways:
One-to-One: One record from one table matches another record from a different table.
One-to-Many: One record from one table matches many records from another table. An example: An order tracking database that has the Customer table and an Orders table. The customer is able to place many numbers of orders and follows that for any customer is the customer table. There can be many orders that are represented in the Orders table. So the relationship between the two tables is one-to-many relationships.
Many-to-Many: Many records from one table matches many records from another table.
One-to-one relationships
In a 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.
This kind of relationship is not common, because most information that is related in this manner would be in one table. You might use a one-to-one relationship to take the following actions:
- Divide a table with many columns.
- Isolate part of a table for security reasons.
- Store data that is short-lived and could be easily deleted by deleting the table.
- Store information that applies only to a subset of the main table.
In Access, the primary key side of a one-to-one relationship is denoted by a key symbol. The foreign key side is also denoted by a key symbol.
Handling Many-To-Many Relationships in Microsoft Access:
Definition: Many-to-many (M:M) – is used to relate many records in the table A with many records in the table B. A record (‘parent’) in Table A can have many matching records (‘children’) in Table B, and a record (‘child’) in Table B can have many matching records (‘parents’) in Table A.
It is the hardest relationship to understand and it is not correct. By breaking it into two one-to-many relationships and creating a new (junction/link) table to stand between the two existing tables will enable correct and appropriate relationship setting.
A many-to-many relationship is really two one-to-many relationships with a junction/link table.