Connects two tables together by their common field in the relationships window.

This chapter is from the book 

Defining Table Relationships

Data from several different tables is related through the use of common fields. A common field is a field existing in two or more tables, allowing you to match records from one table with records in the other tables. Once you have a way of relating two tables with a common field, your next task is to express the nature of that relationship. There are three types of relationships: one-to-one, one-to-many, and many-to-many. You can define and manage relationships using buttons on the Database Tools tab. This gives you control over your table relationships and also gives you a quick snapshot of all the relationships in your database. After you define a relationship, you can double-click the connection line to modify or add to the relationship.

Define Table Relationships

• 
  Click the Database Tools tab.
• 
  Click the Relationships button.

  If relationships are already established in your database, they appear in the Relationships window. In this window, you can create additional relationships.

• 
  If necessary, click the Show Table button to display the Show Table dialog box.
• 
  Click the Tables tab.
• 
  Click the table you want.
• 
  Click Add.

  The table or query you selected appears in the Relationships window. Repeat steps 5 and 6 for each table you want to use in a relationship.

• 
  Click Close.
• 
  Drag the common field in the first table to the common field in the second table. When you release the mouse button, a line appears between the two tables, signifying that they are related. Also, the Edit Relationships dialog box opens, in which you can confirm or modify the relationship.
  

  Click to view larger image

• 
  Click the Join Type button if you want to specify the join type. Click OK to return to the Edit Relationships dialog box.

Click Create to create the relationship.

Join Types

There are three types of table relationships you can have in Access (One-to-Many, Many-to-Many, and One-to-one), however, the One-to-Many is the most useful for the beginner, so that is what we will look at, here.  A One-to-Many relationship is a relationship between two tables where a record in one table can be used repeatedly as a value in another table.

Creating One-to-Many Relationships between tables

The two most common reasons you will likely come across for creating one-to-many relationships between tables are as follows:

1. To provide a lookup list for a field of a table that will have values that are repeated over and over again.  Example: in the Book Orders example from the Welcome tab, you would likely be ordering multiple books from the same vendors any number of times. Rather than have the user type in the vendor's name each time, you could draw those values from a separate table that only lists those values once.  This would:
   1. Save time and prevent errors likely to occur when users manually input the same values over and over again.  
   2. Allow you to update or change one of those values and have it instantly update in all of its instances on the related table.
2. To create a field in a table that can contain multiple items.  To continue with a similar example, if we were to keep track of video orders for a library, we might have a main table that represents each order we make to a specific vendor, with the date ordered, date received, vendor name, etc.  Within that table, we might want to actually reference the titles on each order.  However, since there would likely be multiple titles on a number of orders, we couldn’t adequately represent that with a single field per record.  Instead, we would want a separate list of titles that reference the order they were part of.

We’ll look at a simple way to do each of these:

1. Using a table to provide lookup values to a field in another table (NOTE: the first two steps assume the table you want to draw values from does not already exist) 
   1. Go to the “Create” tab on the toolbar at the top of the Access window and select “Table” from the row of choices that appears under the tab.
   2. Go to Design View (use the "View" dropdown menu at the top left) and save the table, naming it for the type of items you want in your lookup list (this would be “Vendors” in the book example, above). Then add an additional field (you’ll be keeping the ID field) that will contain your lookup values.  Save the new table again, and then add the items you wish to be in your original table’s lookup column in the datasheet view of the new table.
      
      
   3. Go back to the Design View of your original table.  Select “Lookup Wizard” as the data type of the field for which you want a list created.
      
   4. The Lookup Wizard dialog box will pop up.  On the first screen, you will be asked to choose whether you wish to have the lookup column look up the values in a table or query or whether you want to type those values in yourself.  Choose the first option, then, click “Next.”
   5. The Lookup Wizard will then ask you what table or query will provide your values.  Select the table you just created and click “Next.”
      
      
   6. You will then be asked what fields to use for your lookup values.  Select the field you added in the new table (the "Vendors" Short Text field in this example), by double-clicking the field name.  Click “Next.”
      
      
   7. On this screen, you will be asked to provide a sort order for the items in your combo box.  Select the  field you chose on the previous screen from the first drop-down menu and have them sorted in ascending order (this is the default).  Click “Next.”
      
      
   8. The next screen gives you the option of setting the width of your columns.  Click and drag the right edge of the sample column visible to adjust columns to the width desired.  Keep the “Hide key column” box checked.  Click “Next.”
   9. The last screen gives you the option to change the label for your lookup column (this will usually be unnecessary).  This screen will also give you a check box asking you whether or not to “Enforce Referential Integrity.”  If you check this, Access will prevent you from deleting anything on the “one” side of you relationship that is included in any records on other tables.  For this type of use, it’d probably useful to check it, but not essential. Click “Next” when done.
      
      
   10. This will return you to Design View.  You can now save your table and go back to Datasheet View.  The field you set up lookup values for should now have a combo box drawing values from your second table.
2. Using a second table to provide a subset of data to your first.  (We’ll use the video orders example from earlier.  The goal is to have multiple titles per each order.)
   1. We would need separate tables for orders and for titles.  Here are examples of what each might look like in Design View:
      
      
      

   2. We would then need to find a common attribute to link them.  Since each video order has a unique ID value and each title record is going to be part of a specific order, the best place to start is with the ID field in our Video Orders table. 
   3. In order to link the two tables by the Video Orders ID value, we need to have a field that can reference that value directly on our second table.  We can do this by adding a new field to the “Titles” table called “Video Order ID.”  Because the original datatype is numerical, this needs to be as well.  (NOTE: This will be considered a “foreign key”, as it references the primary key of another table.)
      
      
   4. We now need to create the relationship between the two tables so that Access will recognize that they are connected.  Save and close both tables, select the “Database Tools” tab from the menu at the top of your Access window, then select the “Relationships” icon from the ribbon below.  You should get a pop up box asking you to choose which tables to show.  Add both the “Video Orders” and “Titles” table.  You’ll then get a window like this:
      
      
   5. In order to set up the relationship, click the “ID” field from the “Video Orders” table and drag it to the “Video Order ID” filed in the “Titles” Table.  You will get a dialog box that looks like this:
      
      
   6. Access will automatically create a one- to-many relationship between the two fields.  It will also give you a check box asking you whether or not to “Enforce Referential Integrity.”  For something like the video order list, where one list is entirely dependent on the other, you will want to check this.  That way if you attempt to delete or change something that is part of a relationship, Access will warn you about it. 

Once the relationship is established and saved, you can re-open the form on the “one” side of your one-to-many relationship, and you will see there is a “+” sign next to each record:

Outlook has automatically created a sub-datasheet that can be expanded out to show the records in your related table.  Clicking on each plus sign will expand the sub-datasheet, and show you the related records, and also allow you to type in new related records.

This is useful because the appropriate relationship between the two tables is created automatically when you type into the sub-datasheet of a specific record.  You can enter new data on the related sheet on the “many” side of the relationship, as well, but would just need to make sure the foreign key refers to an actual record that exists.  In this case, it might be useful to connect the two tables through a combo box, so that you can only input values from your associated table.  

What is the most common type of relationship between two tables?

How are relationships indicated in the relationships window?

Which field is always on the one side of a one

What key field is the linking field on the many side of the relationship?