MS Access - One-To-One Relationship

Let us now understand One-to-One Relationship in MS Access. This relationship is used to relate one record from one table to one and only one record in another table.

Let us now go to the Database Tools tab.

Click on the Relationships option.

Select tblEmployees and tblHRData and then click on the Add button to add them to our view and then close the Show Table dialog box.

To create a relationship between these two tables, use the mouse, and click and hold the EmployeeID field from tblEmployees and drag and drop that field on the field we want to relate by hovering the mouse right over EmployeeID from tblHRData. When you release your mouse button, Access will then open the following window −

The above window relates EmployeeID of tblEmployees to EmployeeID of tblHRData. Let us now click on the Create button and now these two tables are related.

The relationship is now saved automatically and there's no real need to click on the Save button. Now that we have the most basic of relationships created, let us now go to the table side to see what has happened with this relationship.

Let us open the tblEmployees table.

Here, on the left-hand side of each and every record, you will see a little plus sign by default. When you create a relationship, Access will automatically add a sub-datasheet to that table.

Let us click on the plus sign and you will see the information that is related to this record is on the tblHRData table.

Click on the Save icon and open tblHRData and you will see that the data we have entered is already here.