Reviewing Table Relationships
When we first discussed creating a database earlier in 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, as discussed in Lesson 11, "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 Lessons2 and 11, 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 21.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.
The more complex your database, the more tables and table relationships the database contains. For example, Figure 21.2 shows a complex company database that contains several related tables.
Figure 21.1. Related tables share a common field.
Figure 21.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.