Basics of Table Relationships

Understanding the Relationships View

In the Relationships View, the lines represent connections between the tables. Lines and symbols highlight to you how your tables are associated:

  • A dense connecting line indicates you’re setting referential data integrity. This has the benefit of keeping your data synced.

Types of Relationships between tables

Relationships between tables come in three varieties:

  • One-to-one. When every item in each table only appears once. E.g., every employee can have only one health insurance cover to use. For further information, see Create one-to-one relationships.
  • One-to-many. When a single item in one table can have a relationship to several items in a different table. For example, each purchase order can be linked to numerous products.
  • Many-to-many. When a single or multiple items in one table can have a relationship with one or more items in a different table. For further info, see Create many-to-many relationships.

One-to-many relationships

One-to-many relationships is the most frequent relationships between tables in effectively designed tables.

Edit a relationship

If you are changing a current database or if you produced your database with a template, you can alter the present relationships to align with your goals.

Note: If the tables you need to access are currently open,  you need to exit them as well as any open datasets or windows that apply them.

  1. Choose Database Tools > Relationships.
  1. Click the line connecting two related tables.

Tip: If  the necessary relationship you need isn’t visible, on the Design tab, in the Relationships group, press All Relationships

  1. On the Design tab, click Edit Relationships.

On the left, Table/Query is the parent table. On the right, Related Table/Query is the child table.

Regardless of whether or not the tables are displayed in this order within the Relationships View, their arrangement in the dialogue box reflects the direction of the line linking them and the relationship.

4. To modify the fields that bond the tables, choose another field under each table shown.     

5. Edit the way Access synchronises your data across tables.

  • Enforce Referential Integrity – To stop irrelevant data and maintain synced references between table relationships, choose this option.
  • Cascade Update Related Fields – Click this to ensure that data in related fields is changed in each of the related tables. 
  • Cascade Deleted Related Records – The choice to opt for this depends on if you need to keep records in some tables despite being removed from other tables.

6. To adapt the relationship between the tables from an inner join to an outer join, press the Join Type button. For further details, read Create queries with outer joins.

 Delete a relationship

1. Click Database Tools > Relationships.

2. Choose the line linking the two related tables.    

3. Press the Delete key. If you are asked to confirm that you wish to delete the relationship, click Yes.

NB: After deleting a relationship, you also cancel referential integrity support for that relationship, if pre-existing. Subsequently, Access will stop blocking changes that lead to orphan records on the many end of a one-to-many relationship.

Want more?

Create, edit or delete a relationship

Leave a Reply

%d bloggers like this: