What is the relationships window

The Relationships window lets you define relationship between items stored in multiple Access tables. It is available by clicking Database Tools > Relationships.

Relationships are like the rules that govern how data is connected in your database. In a well-normalized order-processing database, for example, most likely you'll have customer information in one table and order information in another table. You want to make sure customers don't get separated from their orders, right? And you don't want an order to be created that doesn't have a valid customer connected to it. The Relationships window is where you go to set these rules so that they apply throughout your database.

If you've built an Access query before, you'll notice that the Relationships window looks a lot like the top part of the Query Builder:

In the Relationships window, you use the commands on the Design tab to add tables to the view, and then you drag fields between tables to set up the relationships. In this example, the relationship allows for multiple orders to be connected to each customer, but it keeps you from creating an order for a customer that doesn't exist. Once you set up these rules in the Relationships window, they'll apply to the whole database (for example, if you create a new query, the joins will automatically be set up according to your relationship rules).

Note:  Deleting a table from the Relationships window won't delete it from your database, nor will it remove relationships that you've already established.

Things you can do

Task	How to do it
Add a table or query to the window	Click Design > Show Table, select the table or query you want, and then click Add.
Delete a table or query from the window	Select the table or query, and then press DELETE.
Hide a table or query	Right-click the table or query, and then click Hide Table.
Create a relationship	Drag a field from one table or query to the corresponding field in the other table or query.
Set properties for a relationship	Right-click the relationship line between two tables (this can take a few tries to hit it exactly right), and then click Edit Relationship.
Delete a relationship	Right-click the relationship line between two tables (this can take a few tries to hit it exactly right), and then click Delete.
Show all relationships	Click Design > All Relationships.
View the relationships for a particular table	Select the table, and then click Design > Direct Relationships.
View the relationships for one table without viewing the relationships for other tables	Click Design > Tools > Clear Layout. Add the table you want to see relationships for (Design > Relationships > Show Table), and then click Design > Relationships > Direct Relationships.
Adjust a table size to reveal additional fields and relationships. Note    Requires Access for Office 365.	To automatically size the table, right click a table and select Size to Fit. Or, double-click a table edge on the: Right to size horizontally based on field name length. Bottom to size vertically based on the number of fields. Bottom right corner to auto-adjust the table size, both horizontally and vertically (the equivalent of Size to Fit).
Select a subset of tables Note    Requires Access for Office 365.	To help you focus upon certain tables and their relationships: CTRL + (select multiple tables). Click and drag a box completely around the selected range. Both these actions highlight the subset of tables with a thicker border. To unselect, click outside the selection. To move the selection, click and drag to another location.
Use the mouse scroll wheel Note    Requires Access for Office 365.	Scroll vertically by scrolling the mouse wheel up or down. Scroll horizontally by pressing Shift and then scrolling the mouse wheel up or down.

Now that you're up to speed on the Relationships window, learn how to create, edit or delete a relationship.