Creating a View to Join Tables

Views can be used to join multiple tables.  Joining tables is useful when creating one-to-many relationships. In one-to-many relationships, many records pertain to a single record of another table, for example: a single support ticket (one) can have multiple log entries (many).  If views were not available, all of the important information from one record would have to be continuously copied into each new record.  This extra copying would waste storage space and be more prone to error.

Why join tables?

In this example we are going to join a table containing property listing information with a table containing real estate agent information.  We are using a view so that each property listing will have the real estate agent’s contact information without having to copy that information into each new listing.  Also, all of the agent contact information is centralized so if they change or update it, all of the listing records will refer to the correct data.

Steps to join multiple tables:

1. Make sure your tables are using key values

To join multiple tables these tables must contain key values.  Key values are values that are used to match up records that are associated.  For example, every agent profile contains a field called “Agent_ID”.  Every listing contains a field called “Listing_Agent”.  These values will be used to identify which agent’s information will be associated with which listing.

2. Open the View wizard

In the Views section click on the New View button.  This launches the View wizard.

3. Name your View

Enter a name for your view.  Only letters, numbers, and underscores are accepted (no special characters, spaces, or punctuation).  View names must begin with a letter.

4. Choose the tables you’d like to join

You can join two or more tables as long as they have key values associating them together.  Click Next.

5. Associate the key values

This second screen of the View wizard allows you to associate two fields from the tables. View field names are created by combining the name of the table, a period, and the original field name. Choose your key fields to associate in the left and right dropdowns, and use the radio buttons under each dropdown to indicate which records to include from each table. Press the Add button to include this relationship in the View.

Views support the following join types:

• Inner Join: Include only matching records which are present in both Tables.
• Left Outer Join: Includes all records in Left Table and only matching records in Right Table.
• Right Outer Join: Includes all records from Right Table and only matching records in Left Table.
• Full Outer Join: Includes all records in both tables, even if no other matching record exists.

Join types can also be set at the table-level by creating Relationships.  The Relationships window provides additional data integrity controls and so that field relationships and restrictions are automatically detected when creating Views and DataPages.

6. Choose to make one table editable

The lower portion of this screen allows you to select one of the tables and make it editable.  You can only have one editable table within a view.  Press Next when you are finished.

7. Remove extra fields and filter

The last screen lets you remove any fields that don’t pertain to your current task.  You can also filter your records in this screen.  Press Finish and Close to save your new view.

Summary

In this article we’ve seen how views are used to join multiple tables with different join types.  Views are used so that a single record can access all of the information included in multiple tables.  The tables must be formatted using keys to associate the records of the two tables.