Microsoft Office Tutorials and References
In Depth Information
Establishing Relationships Between Database Tables
✦ Because there is a relationship between the ID field in the Customers
table and the Customer ID field in the Orders table — because the two
fields hold the same type of information — Access can match the 2012
records it finds in the Orders table with corresponding records in the
Customers table. Where the Customer ID of a 2012 record in the Orders
table and an ID in the Customers table match, Access assembles a new
record and places it in the query results.
✦ Data for determining which records appear in the query results is found
in the Order Date field in the Orders table. But the information compiled
in the query results — customer IDs, company names, cities, and
countries — comes from fields in the Customers table. Thanks to the
relationship between the ID and Customer ID fields in these tables, Access can
draw upon information from both tables.
Types of relationships
The vast majority of relationships between tables are one-to-many
relationships between the primary key field in one database table and a field in
another. Table relationships fall in these categories:
✦ One-to-many relationship: Each record in one table is linked to many
records in another table. The relationship in Figure 2-10 is a one-to-many
relationship. Each ID number appears only once in the ID field of the
Customers table, but in the Orders table, the same Customer ID number
can appear in many records because the same customer can order many
different products. When you link tables, Access creates a one-to-many
relationship when one of the fields being linked is either a primary key
field or an indexed field assigned the No (No Duplicates) setting. (See
“Indexing for Faster Sorts, Searches, and Queries” earlier in this chapter.)
✦ One-to-one relationship: Two fields are linked. This relationship is rare
and is sometimes used for security purposes.
✦ Many-to-many relationship: This complex relationship actually describes
crisscrossing relationships in which the linking field is not the primary
key field in either table. To create a many-to-many relationship, an
intermediary table called a junction table is needed. This relationship is rare.
Sometimes, fields in separate tables that hold the same data also have the
same name, but that isn’t necessary. For example, a field called ZIP Code in
one table might be called Postal Code in another. What matters is that fields
that are linked have the same data type. For example, you can’t create a
relationship between a text field and a number field.