Reviewing Table Relationships

When we first discussed creating a database in the Access section of this book, we made a case for creating tables that held discrete subsets of the data that would make up the database. We then discussed the importance of creating relationships between these tables in Lesson 9, "Creating Relationships Between Tables." In this lesson, you take a look at how you can take advantage of related tables when creating other Access objects, such as forms, queries, and reports .

As previously discussed in Lessons 1 and 9, tables are related by a field that is common to each table. The common field serves as the primary key in one of the tables and as the foreign key in the other table. (The foreign key is the same field, but it is held in a table where it does not serve as the primary key.)

For example, in Figure 19.1, an Employees table is linked to two other tables: Expenses and Departments. The Employees table and the Expenses table are related because of the EmployeeID field. The Employees table and the Departments table are related by the DepartmentID field.

Figure 19.1. Related tables share a common field.

The more complex your database, the more tables and table relationships the database contains. For example, Figure 19.2 shows a complex company database that contains several related tables.

Figure 19.2. Complex databases contain many related tables.

More important to the discussion in this lesson is how you take advantage of related tables to create complex forms and reports. First, take a look at how related table data can be viewed in the Table Datasheet view.