- Why This Chapter Is Important
- Introduction to Relational Database Design
- Establishing Relationships in Access
- Establishing Referential Integrity
- Looking at the Benefits of Relationships
- Examining Indexes and Relationships
- Practical Examples: Establishing the Relationships Between the Tables Included in the Time and Billing Database
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
Establishing Relationships in Access
You use the Relationships window to establish relationships between Access tables, as shown in Figure 3.5. To open the Relationships window, click to select the Database Tools tab on the ribbon, and then select the Relationships tool in the Show/Hide group. If you have not established any relationships, the Show Table dialog box appears. The Show Table dialog box allows you to add tables to the Relationships window.
)
Figure 3.5 The Relationships window enables you to view, add, modify, and remove relationships between tables.
Looking at the Relationships window, you can see the types of relationships that exist for each table. All the one-to-many and one-to-one relationships defined in a database are represented with a join line. If you enforce referential integrity between the tables involved in a one-to-many relationship, the join line between the tables appears with the number 1 on the one side of the relationship and with an infinity symbol (∞) on the many side of the relationship. One-to-one relationships appear with a 1 on both ends of the join lines.
Establishing a Relationship Between Two Tables
To establish a relationship between two tables, follow these six steps:
- Open the Relationships window.
- If you're opening the Relationships window of a particular database for the first time, the Show Table dialog box appears (see Figure 3.6). Select each table you want to relate and click Add.
Figure 3.6 The Show Table dialog box enables you to select the tables you want to relate.
- If you have already established relationships in the current database, the Relationships window appears. If the tables you want to include in the relationship do not appear, click the Show Table button in the Relationships group on the ribbon. To add the desired tables to the Relationships window, select a table and then click Add. Repeat this process for each table you want to add. To select multiple tables at once, press Shift while clicking to select contiguous tables or press Ctrl while clicking to select noncontiguous tables; then click Add. Click Close when you are finished.
- Click and drag the field from one table to the matching field in the other table. The Edit Relationships dialog box appears, as shown in Figure 3.7.
Figure 3.7 The Edit Relationships dialog box enables you to view and modify the relationships between the tables in a database.
- Determine whether you want to establish referential integrity and whether you want to cascade update related fields or cascade delete related records by enabling the appropriate check boxes. The section later in this chapter titled "Establishing Referential Integrity" covers these topics.
- Click Create.
)
)
Looking at Guidelines for Establishing Relationships
You must remember a few important points when establishing relationships. If you are not aware of these important gotchas, you could find yourself in some pretty hairy situations:
- It is important to understand the correlation between the Relationships window and the actual relationships you have established within the database. The Relationships window lets you view and modify the existing relationships. When you establish relationships, Access creates the relationships the moment you click Create. You can delete the tables from the Relationships window (by selecting them and pressing Delete), but the relationships still exist. (The "Modifying an Existing Relationship" section, which appears later in this chapter, covers the process of permanently removing relationships.) The Relationships window provides a visual blueprint of the relationships that you have established. If you modify the layout of the window by moving tables around, or by adding tables to or removing them from the window, Access prompts you to save the layout after you close the Relationships window. Access is not asking whether you want to save the relationships you have established; it is simply asking whether you want to save the visual layout of the window.
- When you are adding tables to the Relationships window using the Show Tables dialog box, it is easy to accidentally add the same table to the window many times. The reason is that the tables you are adding can hide behind the Show Tables dialog box, or they can appear below the portion of the Relationships window that you are viewing. If this occurs, you'll see multiple occurrences of the same table when you close the Show Tables dialog box. Access gives each occurrence of the table a different alias. You must remove the extra occurrences.
- You also can add queries to the Relationships window by using the Show Tables dialog box. Although rarely used, this approach might be useful if you regularly include the same queries within other queries and want to permanently establish a relationship between them.
- If you remove tables from the Relationships window (this does not delete the relationships) and you want to once again show all relationships that exist in the database, click All Relationships in the Relationships group on the Design tab. These processes show all existing relationships.
- To delete a relationship, select the join line and click Delete.
Create a new database and add a table called tblCustomers, another called tblOrders, and a third called tblOrderDetails. The tables should have the following fields:
tblCustomers: CustomerID, CompanyName, Address, City, State, ZipCode
tblOrders: OrderID, CustomerID, OrderDate, ShipVIA
tblOrderDetails: OrderID, LineNumber, ItemID, Quantity, Price
- In the tblCustomers table, make the CustomerID field a Text field. Designate the CustomerID field as the primary key. Set the size of the field to 5. Make all other fields Text fields with their default properties.
- In the tblOrders table, set OrderID to the AutoNumber field type. Make the OrderID the primary key field. Make the CustomerID field a Text field with a field size of 5. Set the field type of the OrderDate field to Date/Time, and the field type of the ShipVIA field to Number with a size of Long Integer.
- In the tblOrderDetails table, set the field type of the OrderID field to Number and make sure that the size is Long Integer. Set the field type of the LineNumber field to Number with a size of Long Integer. You should base the primary key of the table on the combination of the OrderID and LineNumber fields. The ItemID and Quantity fields should be the Number type with a size of Long Integer. The Price field should be the Currency type.
- To open the Relationships window, click the Relationships button in the Show/Hide group of the Database Tools tab. With the tblCustomers table in the Show Table dialog box selected, hold down your Shift key and click to select the tblOrders table. Click Add. All three tables should appear in the Relationships window. Click Close. Click and drag from the CustomerID field in the tblCustomers table to the CustomerID field in the tblOrders table. After the Edit Relationships dialog box appears, click Create. Repeat the process, clicking and dragging the OrderID field from the tblOrders table to the OrderID field in the tblOrderDetails table.
Modifying an Existing Relationship
Modifying an existing relationship is easy. Access gives you the capability to delete an existing relationship or to simply modify the nature of the relationship. To permanently remove a relationship between two tables, follow these three steps:
- Click to select the Database Tools tab and then click to select the Relationships tool in the Show/Hide group on the ribbon.
- Click the line joining the two tables whose relationship you want to delete.
- Press Delete. Access prompts you to verify your actions. Click Yes.
You often will want to modify the nature of a relationship rather than remove it. To modify a relationship, follow these four steps:
- Click to select the Database Tools tab, and then click to select the Relationships tool in the Show/Hide group on the ribbon.
- Double-click the line joining the two tables whose relationship you want to modify.
- Make the required changes.
- Click OK. All the normal rules regarding the establishment of relationships will apply.