Creating a Relationship Between Tables

To create a relationship between tables, open the Relationships window. Before you can create relationships between tables, you must first add the tables to the Relationships window. Follow these steps:

  1. graphics/relationships.gif In the database, select Tools, Relationships , or click the Relationships button on the toolbar to open the Relationships window.

  2. If you haven't selected any tables yet, the Show Table dialog box appears automatically (see Figure 9.1). If it doesn't appear, choose Relationships, Show Table .

    Figure 9.1. Add tables to your Relationships window with the Show Table dialog box.

    graphics/81fig01.jpg

  3. Click a table that you want to include in the Relationships window, and then click the Add button.

    graphics/tip_icon.gif

    Well-Designed Databases and Relationships In a well-designed database, every table in the database is related to at least one other table in the database. So, you might want to add all your tables to the Relationships window.


  4. Repeat step 3 to select all the tables you require in the Relationships window, and then click Close . Each table appears in its own box in the Relationships window, as shown in Figure 9.2. Each table box lists all the fields in that table.

    Figure 9.2. Tables in the Relationships window.

    graphics/81fig02.jpg

    graphics/tip_icon.gif

    Enlarge the Table Box If you can't see all the fields in a table's box, drag the table border to make it large enough to see all the fields.


  5. After you have the tables available in the Relationships window, you can create the relationships you want to exist between them. Remember that you must link the tables using a common field. For example, you can link the Customers table to the Orders table using the Customer ID field, as shown in Figure 9.2. Select the common field in the table where it is the primary key (in this case, the Customer table). Drag the field and drop it on its counterpart (the same field name ) in the other table (in this case, Orders). The Edit Relationships window opens (see Figure 9.3).

    Figure 9.3. The Edit Relationships dialog box asks you to define the relationship you're creating.

    graphics/81fig03.jpg

    graphics/cation_icon.gif

    Field Type Matters The fields to be linked must be of the same data type (date, number, text, and so on). The only exception is that you can link a field with an AutoNumber format to another field with a number format.


  6. The Edit Relationships dialog box shows the fields that will be related. It also allows you to enforce referential integrity, which you learn about in the next section. For now, click Create . A relationship is created, and you'll see a join line between the two fields in the Relationships window (see Figure 9.4).

    Figure 9.4. The join line represents a relationship between the two tables.

    graphics/81fig04.jpg

graphics/save.gif When you create relationships between tables, it's important that you save them. Click the Save button on the Relationships toolbar to save the current relationships (and the list of tables available in the Relationships window).



Microsoft Office 2003 All-in-One
Microsoft Office 2003 All-in-One
ISBN: B005HKSHB2
EAN: N/A
Year: 2002
Pages: 660
Authors: Joe Habraken

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net