- Introduction
- Creating a Custom Database Template
- Using a Custom Database Template
- Planning Tables
- Creating Tables in a Database
- Creating a Table by Entering Data
- Creating an Application Part Using a Template
- Importing Data into Tables
- Linking to Data in Tables
- Working with Table Records
- Working with a Table in Design View
- Specifying a Primary Key in Design View
- Planning Table Relationships
- Defining Table Relationships
- Creating and Printing a Table Relationship Report
- Ensuring Referential Integrity
- Identifying Object Dependencies
- Modifying Object Dependencies
This chapter is from the book
This chapter is from the book
This chapter is from the book
Defining Table Relationships
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. 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. You can define and manage relationships using buttons on the Database Tools tab. This gives you control over your table relationships and also gives you a quick snapshot of all the relationships in your database. After you define a relationship, you can double-click the connection line to modify or add to the relationship.
Define Table Relationships
Click the Database Tools tab.
Click the Relationships button.
If relationships are already established in your database, they appear in the Relationships window. In this window, you can create additional relationships.
If necessary, click the Show Table button to display the Show Table dialog box.
Click the Tables tab.
Click the table you want.
Click Add.
The table or query you selected appears in the Relationships window. Repeat steps 5 and 6 for each table you want to use in a relationship.
Click Close.
Drag the common field in the first table to the common field in the second table. When you release the mouse button, a line appears between the two tables, signifying that they are related. Also, the Edit Relationships dialog box opens, in which you can confirm or modify the relationship.
Click the Join Type button if you want to specify the join type. Click OK to return to the Edit Relationships dialog box.
Click Create to create the relationship.
)
)
Join Types
|
Join Types |
Description |
|
Include rows only where the joined fields from both tables are equal |
Choose this option if you want to see one record in the second table for every record that appears in the first table. The number of records you see inthe two tables will be the same. |
|
Include ALL records from “xxx” (the first table) and only those records from “yyy” (the second table) where the joined fields are equal |
Choose this option if you want to see all the records in the first table (even if there is no corresponding record in the second table) as well as the records from the second table in which the joined fields are the same in both tables. The number of records you see in the first table might be greater than the number of records in the second table. |
|
Include ALL records from “yyy” (the second table) and only those records from the “xxx” (the first table) where the joined fields are equal |
Choose this option if you want to see all the records in the second table (even if there is no corresponding record in the first table) as well as the records from the first table in which the joined fields are the same in both tables. The number of records you see in the second table might be greater than the number of records in the first table. |