Relationships between tables in Access

In this article, go with TipsMake.com to learn more about how to establish a relationship.The nn relationship is the relation between two tables, a record of table A can have multiple records matching in table B and vice versa.

To create such a relationship, define the third table, called a join table, with the primary key including foreign keys from both Table A and Table B to divide the nn relationship into two 1-n relationships. .

Continuing with the examples in the previous tutorial, we added a tblTacgia table and a link table tblTacgiaNoi. The Author table includes the schools TacgiaID, Ten, Ho, Diachi, Dienthoai, Email, Ngaysinh.

Relationships between tables in Access Picture 1

Often an author will work with many projects, as well as a project that can have many authors working together. As in the previous tutorials, the tblDuAn table also has an author field, so we will create a separate table that includes information about the author. So, we don't need this field in the Project table anymore.

Relationships between tables in Access Picture 2

Choose Tacgia school , press the delete button and you will see the following message.

Relationships between tables in Access Picture 3

Click Yes .

Now we will create a link board. This connector has two foreign keys as follows:

Relationships between tables in Access Picture 4

These foreign key fields will be primary keys from both tables tblTg and tblDuAn.

To create multiple keys in Access, select both of these fields, go to the Design tab , click Primary Key, the selected fields will become keys.

Relationships between tables in Access Picture 5

Save the table with the name tblTacgiaNoi.

Next, go to the Relationship in the Design tab to create a relationship by clicking Show Table.

Relationships between tables in Access Picture 6

Select the tblDuAn, tblTacgia and tblTacgiaNoi tables, click Add to add all three tables to the desktop and close the Show Table dialog box .

Relationships between tables in Access Picture 7

Using the mouse to click and hold the TacgiaID field in tblTacgia table , drag to the TacgiaID field you want to connect to the table tblTacgiaNoi. When you release the mouse, Access opens the Edit Relationships window . Note, the relationship that is creating Access will be considered a 1-n relationship. Click the Cascade Update box and click Create:

Relationships between tables in Access Picture 8

Result:

Relationships between tables in Access Picture 9

Next, drag the DuAnID field from the tblDuAn table to the DuAnID field of the tableTableGoogle, and check the Enforce Referential Integrity box and Cascade Update Related Fields .

Relationships between tables in Access Picture 10

And this is the relationship we want to establish:

Relationships between tables in Access Picture 11

Previous article: 1-n relationship between tables in Access

Next lesson: Wildcard representation operator in Access