Using the Relationships Window


Remember that Access is a relational database. Objects in your database are related to one another through relationships defined by common fields between tables. There are three types of relationships: one-to-many, one-to-one, and many-to-many.

One-to-many relationships are the most common. In a one-to-many relationship, the primary table contains a primary key field that is included as a field (the foreign key) in the secondary table. Thus, one record in the first table can relate to many records in the second table. When these fields have the same names, Access automatically creates the one-to-many relationship for you. However, the fields may have different names. In those cases, you may want to manually create the relationship using the relationships windows.



To view and define relationships between tables:

  1. Open the relationships window by clicking the Relationships toolbar button.
  2. You can see existing relationships by the lines connecting field names.
  3. To create a new relationship, click the primary key field name in the primary table and drag to the related field name in the secondary table.
  4. The Edit Relationships window will automatically open.
  5. Review the relationship and click the Create button.


To display a table or query that isn't already showing in the relationships window:

  1. Click the Show Table... toolbar button.
  2. Select Show Table... from the Relationships menu.
  3. Right-click anywhere in the relationships window and select Show Table... from the shortcut menu.

You can delete relationships between tables. In the relationships window, right-click on the relationship line and select Delete from the shortcut menu. Be careful when deleting relationships.

To open the Relationships window, you can also:

  1. Select Relationships... from the Tools menu.
  2. Right-click anywhere in the database window and select Relationships...
You can also create relationships in the relationships window by:
  1. Opening the Edit Relationships dialog box.
  2. Clicking the Create New button.
  3. Selecting the tables and columns (fields) you want to join. Click OK.