Relationships between tables in Access

In this article, let’s learn about how to establish relationships with Quantrimang.com. Relationships is the relationship between two tables where a record in table A can have many matching records in table B and vice versa.

To create such a relationship, define a third table, called a junction table, whose primary key includes foreign keys from both table A and table B to split the relationship nn into two 1-n relationships .

Continuing with the examples in the previous tutorial, we add a table tblTacgia and a junction table tblTacgiaNoi. Board Author including fields TacgiaID, Ten, Ho, Diachi, Dienthoai, Email, Naysinh.

Often an author will work on multiple projects, just as a project can have multiple authors working on it. As in previous tutorials, the table tblDuAn There’s also an author field, so we’ll create a separate table that includes author information. So we don’t need this field in the table Project again.

Select field Tacgia, press the delete button and you will see the following message.

Click Yes.

Now we will create a junction table. This junction table has two foreign keys as follows:

These foreign key fields will be primary keys from the two tables tblTacgia and tblDuAn.

To generate keys for multiple in Access, select both of these fields, go to tab Thiết kế, click Primary Key, selected fields will all become keys.

Save the board with the name tblTacgiaNoi.

Next, go to Relationship in tab Thiết kế to create a relationship by clicking Show Table.

Select table tblDuAn, tblTacgia and tblTacgiaNoi, click Add to add all three panels to the desktop and then close the dialog Show Table.

Use mouse to click and hold the field TacgiaID in the table tblTacgia, link to school TacgiaID you want to connect at the table tblTacgiaNoi. When you release the mouse, Access opens a window Edit Relationships. Note, the relationship that Access is creating will be treated as a 1-n relationship. You tick the box Cascade Update and click Create:

Result:

Next, you drag the field DuAnID from the table tblDuAn go to school DuAnID board tblTacgiaNoi, check box Enforce Referential Integrity and Cascade Update Related Fields.

And this is the relationship we want to establish:

Previous article: 1-n relationships between tables in Access

Next article: Wildcard wildcard operator in Access