When you place data into separate tables, you need some way of merging this data together for forms and reports . You can do this by establishing table relationships that indicate how data in one table relates to data in another.
Specifying Common Fields
Data from several different tables is related through the use of common fields. A common field is a field existing in two or more tables, allowing you to match records from one table with records in the other tables. For example, the Customers table and the Orders table might both contain a Customer ID field, which functions as a primary key that identifies a specific customer. Using Customer ID as a common field allows you to generate reports containing information on both the customer and the orders the customer made. When you use a primary key as a common field, it is called a foreign key in the second table.
Building Table Relationships
Once you have a way of relating two tables with a common field, your next task is to express the nature of that relationship. There are three types of relationships: one-to-one, one-to-many, and many-to-many.
A table containing customer names and a second table containing customer addresses exist in a one-to-one relationship if each customer is limited to only one address. Similarly, a one-to-many relationship exists between the Customers table and the Orders table because a single customer could place several orders. In a one-to-many relationship like this, the "one" table is called the primary table , and the "many" table is called the related table .
Finally, if you allow several customers to be recorded on a single order (as in the case of group purchases), a many-to-many relationship exists between the Customers and Orders tables.
Maintaining Referential Integrity
Table relationships must obey standards of referential integrity , a set of rules that control how you can delete or modify data between related tables. Referential integrity protects you from erroneously changing data in a primary table required by a related table. You can apply referential integrity when:
Referential integrity places some limitations on you.
Access can enforce these rules by cascading any changes across the related tables. For example, Access can automatically copy any changes to the common field across the related tables. Similarly, if a record is deleted in the primary table, Access can automatically delete related records in all other tables.
As you work through these issues of tables, fields, and table relationships, you will create a structure for your database that will be easier to manage and less prone to data-entry error.