Creating a relationship (Access '97)

From Yacapaca wiki
Jump to: navigation, search

A relational database uses more than one table. The tables must contain data that is in some way connected. The reason for using a relational database is so that data does not have to be typed in more than once.

The following example shows how a simple relational database can be set up.

  • The database shows a number of employees for PMS software and links them to the office that they work in.
  • There are two tables in this database: the first one contains the data about each employee, and the second one contains the location of each PMS software office.

Making the Database

Create a database and call it ‘PMS Software’. Make two tables like the ones shown below.

Employee table.gif

Office location.gif

In order for the tables to be linked, each one has to have a Primary Key field. The primary key sets the one (or more) field that makes each record unique.

The primary fields for these tables are shown by a little key on the left-hand end of the field row. Insert the primary keys, by right-clicking on the little grey button at the beginning of the field row and selecting Primary Key from the menu that pops out.

Primary key 1.gif

You now have two tables with one common field - the Office Number field. This will be the field that links the tables (makes the relationship).

Select the Relationships button on the second toolbar - Relationship 1.gif

The Relationships window and the Show Table window will appear.

Show table.gif

Click on each table name in the Show Table window and select Add. This will put them onto the Relationships window. When you have done this, close the Show Table window.

To make the actual relationship, drag and drop the Office Number field from the Office Location table to Office Number in the Employee Table.

When you have done that, another window called Relationships will appear.

Referential integrity.gif

You have to select Enforce Referential Integrity and make sure that the relationship type is One to Many, then select Create. The window will go, leaving you with the original Relationships window, looking like the one below.


Close and save the Relationships window.