- 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
You use the Relationships window to establish relationships between Access tables, as shown in Figure 3.5. To open the Relationships window, click Relationships on the toolbar with the Database window active or choose Relationships from the Tools menu. If you have not established any relationships, the Show Table dialog appears. The Show Table dialog allows you to add tables to the Relationships window.
Looking at the Relationships window, you can see the type of relationships that exists 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 (x) on the many side of the relationship. One-to-one relationships appear with a 1 on both ends of the join lines.
)
Figure
3.5 The Relationships window enables you to view, add, modify, and remove
relationships between tables.
Establishing a Relationship Between Two Tables
To establish a relationship between two tables, follow these six steps:
Open the Relationships window.
If it is the first time that you're opening the Relationships window of a particular database, the Show Table dialog appears. Select each table you want to relate and click Add.
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 on the toolbar or choose Show Table from the Relationships menu. 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 appears, as shown in Figure 3.6.
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 entitled "Establishing Referential Integrity" covers these topics.
Click Create.
)
Figure
3.6 The Edit Relationships dialog enables you to view and modify the relationships
between the tables in a database.
Looking at Guidelines for Establishing Relationships
You must remember a few important things 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 will 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/removing tables to 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 adding tables to the Relationships window using the Show Tables dialog, it is easy to accidentally add the same table to the window many times. This is because the tables you are adding can hide behind the Show Tables dialog, 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. 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. Although rarely used, this 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 Show All Relationships on the toolbar or choose Show All from the Relationships menu. These processes show all existing relationships.
To delete a relationship, click the join line and press 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, 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 Relationships on the toolbar with the Database window active. With the tblCustomers table in the Show Table dialog 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 Relationships dialog appears, click Create. Repeat the process, clicking and dragging the OrderID field from the tblOrders table to the OrderID field in the tblOrderDetails table.
NOTE
You can find this example, and all examples included in this chapter, in the Chap3TryIt.MDB file included with the sample code on the accompanying CD-ROM.
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:
With the Database window active, click Relationships on the toolbar.
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:
With the Database window active, click Relationships on the toolbar.
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.