Relationships

Using Relationships

Imagine you work in a large corporation with thousands of employees. Your job is to manage information about employees. For simplicity we will use a small number of records and limited fields. The information in the Table below represents the company database. All information about employees, their contact information, salary scales, etc is all contained in the one database.

A database that contains ALL information in one Table is called a "Flat File" database.

For reasons of confidentiality and ease of use it is preferable to have the data in separate Tables. The following three Tables ("Personal Details", "Salary Details" and "Salary Scales" contain the same information as the Flat File Table, but now it is separated into logical groupings.

The problem now is how to tell Access that these separate Tables are actually all "related" together.

Personal Details Table

Salary Details Table

Salary Scales Table

Relationships between Tables are established using "Primary Keys". For one unique field in each Table select Edit/Primary Key. An icon of a small key appears next to each Primary Key Field.

Selecting the Primary Key ...

An icon of a small Key appears next to the Primary Key ... (in this case: EmpoyeeID)

The Primary Keys chosen in this example are:

Table	Primary Key Field
Personal Details	EmployeeID
Salary Details	EmployeeID
Salary Scales	SalaryScale

When you go to the Query window, the relationships you established by defining Primary Keys are represented by lines between fields in the Field List windows.

Access is now able to "relate" the information in each Table.

Referential Integrity

Now that our Tables are so intimately connected, it is important that you don't make any 'accidental' changes to data in one Table that impact on other Tables. If, for example, you changed the Salary Scales from "A", "B", "C", "D" and "E" to "1", "2", "3", "4", and "5" you would no longer be able to relate employee names with actual salaries.

To prevent such changes, Access uses the concept of "referential integrity" which will prevent changes taking place to one Table that would have a detrimental impact on related Tables. To set Referential Integrity click the Enforce Referential Integrity box when first establishing Table relationships....