Creating a relationship (Access '97)
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.
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.
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).
The Relationships window and the Show Table window will appear.
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.
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.