MS Access —

Review: Understanding Table Relationships

Make sure that you have a good understanding of table relationships.

Creating Relationships between Tables

To Define Table Relationships: In the Database window, click the Relationships button on the toolbar or right-click any blank area in the Database window and select Relationships. If necessary, click the Show Table button on the toolbar, click the table you want, and click Add. Repeat for each for table you want to use in the relationship and click Close when you're finished. Click the related field in the first table and drag it to the related field in the second table, specify any referential integrity and/or join type options (optional). Click Create to create the relationship.

Enforcing Referential Integrity

To Enforce Referential Integrity: In the Database window, click the Relationships button on the toolbar or right-click any blank area in the Database window and select Relationships. Double-click the join line for the relationship you want to work with and check the Enforce Referential Integrity box. If you want changes to the primary field of the primary table copied to the related field in the related table, check the Cascade Update Related Fields box, or if you want Access to automatically delete orphan records in the related table, check the Cascade Delete Related Records box. Then Click OK.

Testing Referential Integrity and Printing and Deleting Table Relationships

To Print the Relationships Window: In the Database window, click the Relationships button on the toolbar or right-click any blank area in the Database window and select Relationships. Select File » Print Relationships from the menu. Modify the report that appears in Print Preview, if necessary, and then click the Print button on the toolbar.

To Delete a Table Relationship: Open the Relationships window, click the join line that connects the tables, and press Delete. Click Yes to confirm the deletion.

Quiz

  1. You can use fields with different data types to link two tables. (True or False?)

  2. Which of the following statements is NOT true?

    1. Click the Relationships button on the toolbar to display the table relationships in a database.

    2. Once you have added the required tables to the Relationships window, you can relate the tables by clicking the related field in the first table and dragging it to the related field in the second table.

    3. Referential integrity keeps records in related fields valid and accurate.

    4. For most people, table relationships are an easy-to-understand, straightforward concept.

  3. You have created a relationship between a Customers table and a related Customer Orders table and have established referential integrity in this relationship without the Cascade Delete Related Records option. Can you delete a record in the Customers table if it has related records in the Customer Orders table? (Yes or No?)

  4. If the Cascade Delete Related Records referential integrity option is selected, when you delete a record in the main table, Access will automatically delete any matching records in the related table. (True or False?)

  5. How can you display the relationships in a database?

    1. Select View » Relationships from the menu.

    2. Click the Relationships button in the Database window.

    3. Click the Relationships button on the toolbar.

    4. Select Edit » Relationships from the menu.

  6. How can you add a table to the Relationships window?

    1. Select Edit » Add Table from the menu.

    2. Click the Show Table button on the toolbar.

    3. Select the table from the Table list on the toolbar.

    4. Select Tools » Add Table from the menu.

Homework

  1. Open the Homework database.

  2. Open the Customers table in Design View. Set the SSN field as the table's primary key (if it isn't already). Save your changes and close the Customers table.

  3. Using the Relationship window, establish a relationship between the Customers table and the Insurance Claims table, using the SSN field to join the two tables.

  4. Enforce referential integrity for the Customers/Insurance Claims relationship.

  5. Enable Cascade Update Related Fields and Cascade Delete Related Records for the Customers/Insurance Claims relationship.

  6. Close the Relationships window.

  7. Close the Homework database.

Quiz Answers

  1. False. With the exception of AutoNumber and Number fields, related fields must always have the same data type in order to be used to join two tables.

  2. D. If only this were true! The truth is, understanding how to create and work with table relationships is a concept that most users struggle with initially.

  3. No. Referential integrity would prevent you from deleting a record in a primary table if it has records in a related table (unless the Cascade Delete Related Records option is selected).

  4. True. The Cascade Delete Related Records option automatically deletes records in any related tables.

  5. C. Click the Relationships button on the toolbar to display the relationships in a database.

  6. B. You can add a table to the Relationships window by clicking the Show Table button on the toolbar.