|
Relationships Home Microsoft Excel Microsoft Access Custom Reports SQL Server Visual Basic |
Creating relationships in Access between tables simplifies the design of queries. After tables are related in the
relationship designer, queries, forms, and reports will recognize the table association without any further effort.
To set up relationships, choose the Database Tools tab, then choose the Relationships button. The Relationships window will appear, with the Show Table dialog box open: 
Add both tables to the window, then close the Show Table window. With the two tables now side by side, you are ready to create the relationship. Remembering from the discussion of tables that data is stored in separate tables to reduce redundancy and improve performance, this database is set up with the customers' preferences in a different table from the main customer data. The relationship is that there are zero or more records in the FavoriteProduct table for each customer record in the Customers table. The Customer is uniquely identified by the primary key; the FavoriteProduct record is related to the Customer record by the CustomerID in each table. Therefore, the goal is to identify that in this database. Accomplish this by clicking on the CustomerID field in the Customers table, and dragging it onto the CustomerID field in the FavoriteProduct table. Access presents the Edit Relationships dialog box: 
Before creating the relationship, consider the "Referential integrity" check boxes. Referential integrity means that, if selected, Access will enforce rules to keep the tables related properly. Consider if a customer had related preferences. If, for some reason, the database user desired to change the customer's ID number, there would be a potential for the related preference records to no longer have a match in the Customers table. This would create what are called "orphaned" records, as they have no parent record. Once the Enforce Referential Integrity check box is checked, a choice must be made on how to handle these issues. If Enforce Referential Integrity is checked, but Cascade Update Related Fields is unchecked, Access will not allow you to change CustomerIDs in the Customer table if there are related records in the FavoriteProduct table. If Cascade Update Related Fields is selected, Access will change all the related records in other tables if you change the CustomerID in the Customers table. It should be noted that this is an academic discussion for this database, as the CustomerID in the customer table is an Autonumber data type, and cannot be changed. The Cascade Delete Related Records checkbox works similarly; if the box is unselected, Access will disallow any attempt to delete a customer record where there is an associated FavoriteProduct record. If the checkbox is selected, Access will delete all related FavoriteProducts records if the user deletes the customer record. It may seem drastic to delete related records. However, if you deleted a customer record and did not delete the related records, they would be meaningless anyway, as their "parent" record would be gone; you would have FavoriteProducts, but no record of which customer has the record for the favorite product. For most database users, it is advisable to check the Enforce Referential Integrity checkbox. If the database is designed well, there will not be a need to change a primary key (if you ever need to change a primary key, unless someone made a keying error, the database is poorly designed. A proper primary key always identifies a record; if it changes, it was not a good primary key.) The Cascade Delete checkbox is probably advisable, unless you, like I, consider that deleting records in a database is a bad thing; I generally use an Active field (Yes/No data type) to hide inactive records, rather than delete them. Access help is available in the Denver area by calling (303) 403-0386, or submit an Access question by e-mail. Next: Putting data together with an Access query. |
Copyright 2012 Front Range Data Management Services