Use relationships

Create relationships with the Lookup Wizard

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

The Lookup Wizard establishes a relationship between tables. It creates a foreign key that refers back to the primary key of another.

Create a relationship with the Lookup Wizard

  1. In Datasheet View, open the table that will look up data from another table. This is your child table.

  2. In the rightmost column, select Click to Add and then select Lookup & Relationship.

    Add a new table column, type "Lookup and Relationship"
  3. In the Lookup Wizard, select I want the lookup field to get the values from another table or query. Then, select Next.

  4. Select the table that will provide the values for your lookup field. This is your parent table. Then, select Next.

  5. To select the fields that you want to pull data from, set the sort order for the fields, and set the column width, follow the instructions on the page.

    Tip: When you set the column width, to reduce visual clutter in the table, select Hide Key Column. It won’t affect the relationship or data syncing between the tables.

  6. Select a field that uniquely identifies rows in the parent table.

  7. Enter the name for your new lookup field, select Enable data integrity, and then select either Cascade Delete or Restrict Delete.

    Tip: Name the field in a way that is reflective of the data, and note that it’s a foreign key field, for example, Customer IDFK.

  8. Select Finish, and then select Save Save .

You’ve created a relationship and a foreign key and specified data-syncing parameters between the two tables.

Change a relationship between tables

If you want to change the table relationship, first close the tables.

  1. Select Database Tools > Relationships.

  2. Select the relationship line between the two tables.

    A one-to-many relationship
  3. Select Design > Edit Relationships.

  4. In the Edit Relationships dialog box, make your changes and then select OK.

  5. To save your changes, select Save Save .

Want more?

Create, edit or delete a relationship

One of the easiest ways to create a one-to-many relationship is to use a tool called the LOOKUP WIZARD. The wizard actually does two jobs for you.

It creates the relationship and when you're done, you can use the foreign key field to choose values from the table on the ONE side, or the parent table, of the relationship.

This makes it easier to enter data. In our example, all we have to do is select a name that's the value on the ONE side, and enter a phone number.

When you use the wizard, you don't need a foreign key field in your child table because the wizard creates it for you. But if you already have a foreign key, that's okay, go ahead and use it.

Here's a sample database. It does not have a foreign key.

To start, select the heading on a blank field or select the heading in your foreign key, if you have one.

Choose Lookup & Relationship and the wizard starts.

Use the first option, to look up values from another table, and select Next.

Select the parent table… Customers… and then select Next.

We need to look up the values in the Customer Name field, so select that and move it over. This is a calculated field.

We used this field about a minute ago. Notice that it shows the entire name. This takes the guesswork out of choosing the right data. With a lookup field, data entry is faster and more accurate.

Choose Next. Sort order is optional, so let’s skip this and just select Next.

As a rule, it’s best to hide the key column because it just clutters up your database if it's visible, so select Next.

Here, let’s name the field Customer ID FK, which indicates the parent field is Customer ID and FK reminds us that this is a foreign key.

Now, let’s select Enable Data Integrity and Cascade Delete. This is how you enable referential integrity and cascading deletes. Select Finish and there is your field.

Finally, let’s make a change to the relationship. To do this, we need to close the phone numbers table. You can't change a relationship if one of the tables involved in that relationship is open.

After we close the table, on the Database Tools tab we select Relationships. We select the relationship line, and then, on the ribbon, we select Edit Relationships.

You can see that Referential Integrity and Cascading Deletes are both selected. Let’s also enable the middle option, Cascade Update Related Fields, select OK and we're done.

We’ve now created a one-to-many relationship by using the Lookup Wizard, we’ve created the foreign key, and we’ve changed a relationship setting.

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.