Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
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

  • yellow_1.jpg Click the Database Tools tab.
  • yellow_2.jpg 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.

  • yellow_3.jpg If necessary, click the Show Table button to display the Show Table dialog box.
  • yellow_4.jpg Click the Tables tab.
  • yellow_5.jpg Click the table you want.
  • yellow_6.jpg 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.

  • yellow_7.jpg Click Close.
  • yellow_8.jpg 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.
  • yellow_9.jpg Click the Join Type button if you want to specify the join type. Click OK to return to the Edit Relationships dialog box.
  • yellow_10.jpg 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.

    • + Share This
    • 🔖 Save To Your Account