This chapter is from the book
This chapter is from the book
This chapter is from the book
Establishing Relationships in Access
Relationships between Access tables are established in the Relationships window, 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 no relationships have been established, the Show Table dialog appears. The Show Table dialog allows you to add tables to the Relationships window.
){kind=link}
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 referential integrity has been enforced 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 open the Relationships window of a particular database, the Show Table dialog box 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 done.
-
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.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. These topics are covered in the section, "Establishing Referential Integrity."
Click Create.
){kind=link}
Figure 3.6 The Edit Relationships dialog box 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, the actual relationship is created 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 (permanently removing relationships is covered in the "Modifying an Existing Relationship" section of this chapter). The Relationships window provides a visual blueprint of the relationships that have been established. If you modify the layout of the window by moving around tables, adding tables to the window, or removing tables from the window, you are prompted 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 box, 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 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. Each occurrence of the table is given 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 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. This button shows 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 another called tblOrderDetails. The table 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, set the CustomerID field as the primary key. Set the size of the field to 5. All other fields can be left with their default properties.
In the tblOrders table, set OrderID to the AutoNumber field type. Make the OrderID the primary key field. Set the length of the CustomerID field to 5. Set the field type of the OrderDate field to Date.
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 type of the LineNumber field to Number with a size of Long Integer. The primary key of the table should be based on the combination of the OrderID and LineNumber fields. The ItemID and Quantity fields should be Number type with a size of Long Integer. The Price field should be 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 box selected, hold down your Shift key and click to select the tblOrders table. Click Add. All three tables should be added to 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 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.
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. You are asked 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 on 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.