• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Lesson 10. Creating Relationships Betwee... > Creating a Relationship Between Tabl...

Creating a Relationship Between Tables

To create a relationship between tables, open the Relationships window and add relationships there. Follow these steps:

  1. 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 10.1). If it doesn't appear, choose Relationships, Show Table, or click the Show Table toolbar button.

    Figure 10.1. Add tables to your Relationships window with this dialog box.

  3. Click a table that you want to use for a relationship, and then click theAdd button.

  4. Repeat step 3 to select all the tables you want, and then click Close. Each table appears in its own box on the Relationships window, as shown in Figure 10.2.

    Figure 10.2. I've added two tables to my Relationships window for this example.


    Make It Bigger If you can't clearly see all the fields in a table's list, drag the table border to make it large enough to see everything. I've done that in Figure 10.2 to make the Dogs table completely visible.

  5. Click a field in one table that you want to link to another table. For instance, I'm going to link the Coloring field in my Dogs table to the Coloring ID field in my Dog Coloring table, so I'll click the Coloring field in the Dogs table.


    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; AutoNumber fields are considered Long Integer number fields.

  6. Hold down the mouse button and drag away from the selected field. Your mouse pointer turns into a little rectangle. Drop the little rectangle onto the destination field. For instance, I'm dragging to the Color ID field in the Coloring table. The Edit Relationships dialog box appears (see Figure 10.3).

    Figure 10.3. The Relationships dialog box asks you to define the relationship you're cerating.


    Matching Field Names Although it's not required, you will find it easier to match up linking fields if you give them the same name in both tables.

  7. Choose any referential integrity options (see the following section), and then click Create. A relationship will be created, and you'll see a line between the two fields in the Relationships window (see Figure 10.4.

    Figure 10.4. The line represents a relationship between the two fields.


    Relationship Symbols In Figure 10.4, notice that next to the Dogs table there's an infinity sign (∞), and next to the Dog Coloring table there's a 1. These symbols appear in relationships where Enforce Referential Integrity is turned on. The infinity sign means many—meaning many records in this table can match a single record (hence the 1 sign) in the related Dog Coloring table.


    Multitable Forms In Lesson 16, you learn how to use simple tables, like the Dog Coloring one shown in this lesson, to create a list from which users can select when entering records into a form.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint