MS Access

Course Process

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.

TblHRData

Click on the Relationships option.

Relationships

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.

Add To View

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 −

Edit Relationships

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.

Two Tables Created

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.

Employee 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.

Sub Datasheet

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

Hire Date

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

Entered Data