Part I: Access Building Blocks
106
Although you can’t see the relationship in Figure 3.8, Access knows it’s there because a formal rela-
tionship has been established between tblEmployees and tblPayroll (this process is
described in the “Creating relationships and enforcing referential integrity” section, later in this
chapter). Because of the relationship between these tables, Access is able to instantly retrieve all the
records from tblPayroll for any employee in tblEmployees.The relationship example shown in Figure 3.8, in which each record of tblEmployees is related
to several records in tblPayroll, is the most common type found in relational database systems,
but it’s by no means the only way that data in tables is related. This book, and most books on rela-
tional databases such as Access, discuss the three basic types of relationships between tables:l One-to-onel (^) One-to-many
l Many-to-many
Figure 3.9 shows most of the relationships in the Collectible Mini Cars database.
FIGURE 3.9
Most of the Collectible Mini Cars table relationships
Notice that there are several one-to-many relationships between the tables (for example, tbl-
Sales-to-tblSalesPayments, tblSales-to-tblSalesLineItems, and tblCustomers-
to-tblSales). The relationship that you specify between tables is important. It tells Access how
to find and display information from fields in two or more tables. The program needs to know
whether to look for only one record in a table or look for several records on the basis of the rela-
tionship. tblSales, for example, is related to tblCustomers as a many-to-one relationship.
This is because the focus of the Collectible Mini Cars system is on sales. This means that there will