Access 2016: Create a Relationship
Access allows you to create relationships between tables so that you can query related data from multiple tables.
In relational database terms, a relationship is a situation where multiple tables can contain related data that is linked by a common field.
A relationship consists of a parent table and a child table. The child table references the parent table by having a field that matches a field in the parent table. The child's field is referred to as a foreign key. The parent's field is the primary key.
In a relationship, any data entered into the child's foreign key field must match a value from the parent's primary key field.
By ensuring that the foreign key's data matches data in the primary key, we know that all records in the child table will have an associated record in the parent table.
So we can create a one-to-many relationship between the Albums and Artists table. Our relationship will determine that an artist can have many albums, but an album can only belong to one artist.
So now that we've established the relationship, we can query data across both tables and get meaningful results. For example, we can now look up how many albums an artist has released. Or we could find out which artist released a given album. And more.
Types of Relationships
There are three types of relationships:
- A row in table A can have only one matching row in table B, and vice versa.
- One-to-Many (or Many-to-One)
- 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.
- A row in table A can have many matching rows in table B, and vice versa. This is achieved through the use of a third table (commonly called a junction table) that contains lookup data for both tables.