Chapter 4. Establishing Relationships

Although I discussed relationships at length in Chapter 3, “Understanding Relationships,” I didn't show you how to formally create them in an Access database. In this chapter, you will learn how to establish relationships in the Relationships window. I also explain the three key options that are available in the Edit Relationships dialog box: most notably Enforce Referential Integrity, but also Cascade Update Related Fields and Cascade Delete Related Records.

In affairs of the heart, you'll often hear someone say, “I'm currently in a relationship.” Whether the happy couple is in a state of “significant othership” or whether they are merely “erfriends” (as in “Mom and Dad, I'd like you to meet my, er, friend) remains unclear and ambiguous.

In stark contrast, relationships in Access tables are always defined with clarity, and there is no ambiguity about the rights and responsibilities of each partner. There is a plain old vanilla relationship, respectful and generous, but which still leaves both parties with plenty of independence. A relationship can be strengthened immeasurably by enforcing referential integrity, which binds the tables closely together, as if in marriage. Finally, the matrimonial vows can be transformed by enforcing cascading deletes and updates between the tables.