Getting to Know Access 2003, Part 3 - Relationships
(Page 5 of 5 )
Relating your tables is important because it speeds up routine information retrieval and reduces errors in data entry. Moreover, it is useful because related tables can be used to add corresponding subforms and subreports (more on those in a later lesson). Finally, relationships between tables help enforce data integrity.
It’s best to define relationships between your tables when you first create the tables – before you have any data entered. However, you can define relationships at any time.
To create a relationship between two tables you just need to tell Access which fields they have in common. Generally, in the oft-used one-to-many relationship the parent table has a primary key that relates to a foreign key in another table. A foreign key is essentially a field with values that match those in the other table’s primary key but are not necessarily unique.
If you used the Table Wizard to create your tables you might already have relationships created, but either way you can see by choosing the Tools | Relationships menu option. If no relationships are currently defined you’ll be shown the list of tables and queries dialog box from which you can choose the objects you want to relate. Once you have done so your tables or queries are placed in the Relationships window. The primary key of each object is bolded. You’ll see some new toolbar button options at the top of the window too, such as Show Table and Show All Relationships; explore these new buttons. The Relationships page looks similar to the following from the Northwind Sample Database:
You can draw a relationship line by selecting and dragging a field name (usually the primary key) from one table or query to an identical field name in another table or query (usually a foreign key).
You set referential integrity by right-clicking a relationship line and checking the Enforce Referential Integrity checkbox in the dialog box that opens. Once you set referential integrity you also have the option of checking "Cascade Update Related Fields" and "Cascade Delete Related Records." The first option lets you make changes to the primary key in the parent table; Access will then automatically update the child tables to reflect the change. The second option lets you delete parent records; Access will then automatically delete the child records when you delete their parents. The dialog box to set referential integrity looks like the following:
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
