Microsoft Access Relationships refers to 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-OneOne record from one table matches another record from a different table.
• One-to-ManyOne record from one table matches many records from another table. Example: An order tracking database with a Customer table and an Orders table. Each customer can place many orders, so the relationship between the two tables is one-to-many.
• Many-to-ManyMany records from one table match many records from another table.

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 uncommon because most related information would typically reside in a single table. You might use a one-to-one relationship to:

• 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.
• 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.

Definition: Many-to-many (M:M) relates many records in table A with many records in 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.

Many-to-many relationships are more complex and cannot be directly implemented correctly. By breaking it into two one-to-many relationships and creating a new junction (link) table between the two existing tables, you can set up the relationship correctly.

Essentially, a many-to-many relationship is implemented as two one-to-many relationships with a junction/link table.