Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

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 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 () 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:

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

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:

You often will want to modify the nature of a relationship rather than remove it. To modify a relationship, follow these four steps: