Define relationships between tables (MDB)

1. Close any tables you have open. You can't create or modify relationships between open tables.
2. Press F11 to switch to the Database window.
3. Click Relationships on the toolbar.
4. If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed.

   If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar.

5. Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.

6. Drag the field that you want to relate from one table to the related field in the other table.

   To drag multiple fields, press the CTRL key, click each field, and then drag them.

   In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table.

   

7. The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.

   Set the relationship options if necessary.

8. Click the Create button to create the relationship.
9. Repeat steps 5 through 8 for each pair of tables you want to relate.

   When you close the Relationships window, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.

Note  You can create relationships using queries as well as tables. However, referential integrity isn't enforced with queries.

1. Create the two tables that will have a many-to-many relationship.
2. Create a third table, called a junction table, and add to the junction table new fields with the same definitions as the primary key fields from each of the other two tables. In the junction table, the primary key fields function as foreign keys. You can add other fields to the junction table, just as you can to any other table.
3. In the junction table, set the primary key to include the primary key fields from the other two tables. For example, in an Order Details junction table, the primary key would be made up of the OrderID and ProductID fields.

   How?

   1. Open a table in Design view.
   2. Select the field or fields you want to define as the primary key.

      To select one field, click the row selector for the desired field.

      To select multiple fields, hold down the CTRL key and then click the row selector for each field.

   3. Click Primary Key on the toolbar.

   Note  If you want the order of the fields in a multiple-field primary key to be different from the order of those fields in the table, click Indexes on the toolbar to display the Indexes window, and then reorder the field names for the index named PrimaryKey.

4. Define a one-to-many relationship between each of the two primary tables and the junction table.