|
An association that is established between common fields (columns) in two tables is called relationship. Or, Relationships mean a relation into two tables or more. At least two tables are needed for relationships. One is parent table which is control table. And another is child table which is related by parent table.
Types of Relationships:
There are 3 types of relationships.
- One to one
- One to many
- Many to many
One to one: In a one-to-one relationship, each record in Table A can have only one matching record in Table B, and each record in Table B can have only one matching record in Table A.
One to many: In a one-to-many relationship, a record in Table A can have many matching records in Table B, but a record in Table B has only one matching record in Table A.
Many to many: In a many-to-many relationship, a record in Table A can have many matching records in Table B, and a record in Table B can have many matching records in Table A.
About Defining Relationships:
The kind of relationship that Microsoft Access creates depends on how the related fields are defined:
- A one-to-many relationship is created if only one of the related fields is a primary key or has a unique index.
- A one-to-one relationship is created if both of the related fields are primary keys or have unique indexes.
- A many-to-many relationship is really two one-to-many relationships with a third table whose primary key consists of two fields— the foreign keys from the two other tables.
[Note: All related field's data type and value must have to be same.]
| About Primary & Foreign Key: |
|
One or more fields (columns) whose value or values uniquely identify each record in a table.
Properties of Primary key:
- A primary key cannot allow Null values and must always have a unique index.
- A primary key is used to relate a table to foreign keys in other tables.
Foreign key: A foreign key column refers to the values of primary key column of another table.
Properties of Foreign key:
- A foreign key may allow Null values and duplicate values.
- By default, a foreign key reference the primary key attributes of the referenced table.
| To Create Relationships: (One to Many) |
|
Create a new database and create below tables –
Table name:
Authors |
|
Table name:
Books |
|
Fields name: |
Data Type |
Fields name: |
Data Type |
AuthorID (Primary Key) |
Text |
BookID (Primary Key) |
Text |
AuthorName |
Text |
BookName |
Text |
Address |
Text |
Version |
Text |
City |
Text |
Price |
Currency |
Phone |
Text |
Qty |
Number |
|
AuthorID |
Text |
|
|
After create tables –
- On the Tools menu, click Relationships…
- Select Authors table from 'Show Table' list and click Add button > Select Books table and click Add button
- Drag AuthorID field from 'Authors field list' and drop on AuthorID field of 'Books field list'
- Select Enforce Referential Integrity > Click Create button
- Close relationships window > Click Yes button
- Open the Authors table and type data
| To Create Relationships: (One to Many) |
|
Create new database and create below tables –
Table name:
Departments |
|
Table name:
Disease |
|
Fields name: |
Data Type |
Fields name: |
Data Type |
DepID (Primary Key) |
Text |
DiesesID (Primary Key) |
Text |
DepName |
Text |
DiesesName |
Text |
DepLocation |
Text |
Description |
Text |
|
|
DepID |
|
|
|
Table name:
Doctors |
|
Table name:
Patients |
|
Fields name: |
Data Type |
Fields name: |
Data Type |
Doctors ID (Primary Key) |
Text |
PatientID |
Text |
Doctors Name |
Text |
PatientName |
Text |
Fees |
Currency |
Address |
Text |
DiseaseID |
|
City |
Text |
|
|
DoctorsID |
Text |
|
|
After create tables –
- On the Tools menu, click Relationships…
- Select all tables by holding down 'Shift key' and click Add button
- Drag DepID field from 'Departments field list' and drop on DepID field of 'Disease field list' > Select Enforce Referential Integrity > Click Create button
- Drag DiseaseID field from 'Disease field list' and drop on DiseaseID field of 'Doctors field list' > Select Enforce Referential Integrity > Click Create button
- Drag DoctorsID field from 'Doctors field list' and drop on DoctorsID field of 'Patients field list' > Select Enforce Referential Integrity > Click Create button
- Open the Departments table and type data
| To Create Relationships: (Many to Many |
|
Create new database and create below tables –
Table name:
Customers |
|
Table name:
Orders |
|
Fields name: |
Data Type |
Fields name: |
Data Type |
CustomerID (Primary Key) |
Text |
OrderID (Primary Key) |
AutoNumber |
CompanyName |
Text |
OrderDate |
Date/Time |
ContactName |
Text |
ShippedDate |
Date/Time |
ContactTitle |
Text |
ShipName |
Text |
Address |
Text |
ShipAddress |
Text |
City |
Text |
ShipCity |
Text |
Phone |
Text |
CustomerID |
Text |
|
|
Table name:
Order Details |
|
Table name:
Products |
|
Fields name: |
Data Type |
Fields name: |
Data Type |
Order ID (Primary Key) |
Number |
ProductID (Primary Key) |
AutoNumber |
ProductID (Primary Key) |
Number |
ProductName |
Text |
UnitPrice |
Currency |
UnitPrice |
Currency |
Quantity |
Number |
UnitsInStock |
Number |
Discount |
Number |
UnitsOnOrder |
Number |
|
|
Discontinued |
Yes/No |
|
|
After create tables –
- On the Tools menu, click Relationships…
- Select all tables by holding down Shift key and click Add button
- Drag CustomerID field from 'Customers field list' and drop on CustomerID field of 'Orders field list' > Select Enforce Referential Integrity > click Create button
- Drag OrderID field from 'Orders field list' and drop on OrderID field of 'Order Details field list' > Select Enforce Referential Integrity > click Create button
- Drag ProductID field from 'Order Delails field list' and drop on ProductID field of 'Products field list' > Select Enforce Referential Integrity > click Create button
| To Create Relationships: (One to One) |
|
Create new database, create below tables and make relation (EmployeeID to EmployeeID).
Table name:
EmployeeOfficial |
|
Table name:
EmployeePersonal |
|
Fields name: |
Data Type |
Fields name: |
Data Type |
EmployeeID (Primary Key) |
Text |
EmployeeID (Primary Key) |
Text |
LastName |
Text |
Gender |
Text |
FirstName |
Text |
Age |
Text |
Title |
Text |
Address |
Text |
Salary |
Currency |
City |
Text |
JoinDate |
Date/Time |
Division |
Text |
|
|
Phone |
Text |
|
|
Photo |
OLE Object |
|
