Microsoft Access 2007 tutorial:create table relationships
Create table relationships
VIII. Creating the table Relationships
VIII.1. What is relationship?
A Relationship happens between a table and a table through a primary key of a table with a foreign key of another table.
VIII.2. What is Foreign Key?
A Foreign Key is a primary key of a table that exists in another table.
VIII.3. Types of Relationship
In Microsoft Access 2007, there are three types of table relationships. - A one- to-one relationship:
One-to-one relationship is a relationship that each record in the first table can have only one matching to each record of the second table and vice versa. This relationship happens in case you define it. For instance, an employee can have only one legal wife. From this example, you can create two tables:
- TblEmployee(EmpID,EmpName, Sex, Address, Phone,DOB, SpouseID)
- TblSpouse(SpouseID, SpouseName, Sex, DOB, Phone)
The two tables relate to each other throup SpouseID fields. SpouseID in the TbleEmployee is a foreign key referencing to the SpouseID primary key in TblSpouse.
Note – EmpID and SpouseID represent primary keys of the tables. Note
: To create a one-to-one relationship for these two tables, you have set indexed on Foreign Key (Spouse) by selecting Yes (No Duplicates). - A one-to-many relationship:
In one-to-many relationship, one record in the first table can have many matching in the second table. For example, a publisher can publish many books and one book can only publish by a publisher. Through this example, you can create two tables:
-TblPublisher (PubID,PubName, Address)
-TblBook (BookID, PubID,Title, ISBN)
The two tables relate to each other through PubID fields. PubID in the TblBook is a foreign key referencing to the PubID primary key in TblPublisher. - A many-to- many relationship
: In a many-to-many relationship, each record in the first table can have many matching in the second table and each record in the second table can have many matching in the first table. For example, one student can study in one room or many rooms and one room can contain one student or many students. In this case, you can create two tables:
-TblStudent (StudentID, StudentName,Sex,DOB, Addr, Phone)
-TblRoom(RoomID, RoomName, Floor)
In a many-to-many relationship, the third table id created as central table to connect between two tables above. The third table calls tblRoomStudent that contains two Foreign Keys of those two tables as you see on figure below.