Creating Table Relationships

Linking tables

One of the great strengths of Microsoft Access is the ability to link one table to one or more other tables in a database known as relationships. Why? By creating relationships between tables (if it is possible), you can access and view data from one or more tables simultaneously in the same form or report once you have selected a record in your main table. Table relationships can also save you time later when you design queries because Microsoft Access will remember the relationships for you without having to recreate them again for each new query you design.

For example, if you select the name of a person by clicking the correct record in a table, it is possible to link this table to another table showing all the addresses for that particular person. Otherwise, you have to create enough address fields in the original table to hold the data, which is inefficient and makes the original table very messy. By separating the address fields from the name of the individual and placing them into a separate table and then making a relationship between this table and the table for holding the person's name, you can achieve a whole lot more with your database and increase its flexibility.

I've created several tables. How do I create a relationship between them?

The first thing you must do is make sure you can create a Primary Key in one table for linking with another table. You must have a primary key created because the purpose of this key is to ensure a unique piece of data in a field can identify the record selected and thus make it easier for Microsoft Access to pick out data in the other table.

Every table should have a primary key, especially if you intend to create proper relationships with other tables. If it doesn't, you should choose the field or fields that will constitute as the primary key(s). Otherwise, Microsoft Access will create at least one for you (this will be a field with unique numbers automatically entered in sequential order starting from 1).

Once you have one primary key field in one table, you can use it to link to any other non-primary or primary key field in another table.

What types of relationships can I create?

In the technical jargon, table relationships can be of three types: (i) one-to-one relationships (where one primary key field in one table is linked to a primary key field in another table); (ii) one-to-many relationships (where one primary key field in one table is linked with a non-primary key field in another table); and (iii) many-to-many relationships (where a non-primary key field in one table is linked with a non-primary key field in another table).

Each type of table relationship displays data in different ways, depending on what your needs are when creating a database. However, for now we shall concentrate on the simplest and most common type of table relationship, namely one-to-one and one-to-many.

How is the data matched and displayed in these relationship types?

In a one-to-one relationship, each record in the primary table is matched (and displayed) to only one record in the related table. Likewise, each record in the related table is matched to only one record in the primary field. For example, if each and every unique individual in a primary table has only one address in a related table, the link is described as a one-to-one relationship.

In a one-to-many relationship, each record in the primary table is matched (and displayed) to one or more records in the related table. However, each record in the related table is matched to only one record in the primary field. For example, each and every individual in a primary table may have a postal and a street address in a related table.

In a many-to-many relationship, each record in the primary table is matched (and displayed) to one or more records in the related table, and vice versa. For example, a group purchase by multiple individuals on a single order and the group makes several orders would require a many-to-many relationship to described the individuals of the group and the orders made.

Where would I use a one-to-many relationships?

You would use a one-to-many relationship if you need to view in a form or report many records in a second table when a record in the first table is selected. For example, say you have one record with a name for an individual and you want a way to view all the addresses for the individual in another table. Because the second table only hold one address for per record, you want a relationship that allows you to view all the addresses for one individual record.

This is where you would use a one-to-many relationship.

How do I create the relationship?

To create relationships between tables:

  1. Get back into your main Database Window to view the names of all your tables. You do this by pressing the Database window button on the toolbar.
  2. Click on the Relationships button on the toolbar
  3. Microsoft Access displays the Relationships window
  4. If you are creating relationships for the first time, Microsoft Access displays the Add (or Show) Table dialog box as well. Otherwise, click on the Add (or Show) Table button on the toolbar to display it.
  5. The Add Table dialog box appears. Double-click the table or query you want. Microsoft Access adds the table/query to the Relationships window. NOTE: A query is the result of a search conducted on a table resulting in a sub-table.
  6. When you've finished adding tables, click Close.
  7. Now creating table relationships is simply a matter of looking at the fields in each of your tables and see if it already exists in another table. Begin by looking at the primary key field(s) of a table (displayed in bold). Do the names of these field(s) exist in another table? If so, you may want to relate the common fields together.
  8. To create a relationship, or link, between a field in one table and another in a different table, you simply click on the field in the primary table with your mouse and while holding down the mouse button, drag it onto a field containing the same name or data in a related table. Microsoft Access displays the Relationships dialog box with the field names selected already filled in.
  9. Click the Create button. A relationship is officially created between the fields of each selected table. You will see this relationship as a line joining the two related tables.

How do I set rules for the relationship?

Once the relationship has been created, you may want to enforce certain rules (or a standard) to ensure that if you add or delete records in one table, it will or will not affect the related records in the other linked table in the same way. For example, you can enforce the rule that whenever a record is deleted in one table, the second table referenced by it can still keep the related information stored in it. Or perhaps you do want the records in the second table to be deleted. Whatever your choice, such rules are called referential integrity. To specify referential integrity, click on the appropriate check box in the Relationships dialog box.

The lines joining tables provide information as to the type of relationship and rule enforced. If solid horizontal lines join two tables, it indicates referential integrity. And if one end of a line has the number "1" above it and the other end of the line has a symbol known as infinity (or the number "8" lying on its side), then the relationship is "one-to-many".

When can I apply referential integrity?

You can only apply referential integrity when:

  1. the common field linking two tables is the primary key in the primary table;
  2. the common fields related to each other by the relationship have the same format; and
  3. both the primary and related tables belong to the same database.

You should also be aware of some limitations after applying referential integrity to a relationship. Firstly, before you can add a new record to a related table, a matching record must already exist in the primary table. Secondly, the value of a primary key field within the primary table cannot be changed if matching records exist in the related table. And thirdly, a record in the primary table cannot be deleted until the matching records in the related table are deleted first.

But once the referential integrity is turned on and you have matching records, changes you make in the data of the related field can be made to automatically "cascade" across all matching records in the related table. Similarly, if a record is deleted in the primary table, Microsoft Access can automatically delete all matching records in all other related tables in a cascading manner.

For example, if an employee quits his job, you may want to delete all matching records in a related table once the employee's main record in the primary table is deleted. If this is what you want, place a tick in the "Cascade delete related records" check box.

To either update data in a common field in the related table and/or delete related records, click the appropriate check boxes.

What does the Join Type button do?

The Join Type button gives you the option to tell Microsoft Access how to display records in the primary or related table. Choose an option to specify whether you want Microsoft Access to display only one record in the related table for each record in the primary table, or display all the records in the primary table and only those records in the related table where the join fields are the same in both tables, or display the reverse approach.

I've created relationships and now I want to delete a field. How can I do this safely?

There is no one hundred per cent safe way. Nevertheless, Microsoft Access does have a facility to check for dependencies in any database object. The facility involves highlighting a database object and choosing Object Dependencies under the View menu command.

This facility only works if you have turned on the Track Name AutoCorrect Info in the General tab of the Options menu command.