An Introduction to Relationship Types in Access 2007

You can create two types of databases with Access 2007. Flat databases contain only one table and function much like a two-dimensional database not unlike an Excel worksheet. Relational databases, however, make use of multiple tables to reduce redundancy and allow for more powerful queries and reports.

Tables that you wish to relate in some way must have at least one field in common. These fields are used to identify one set of data with another. It is very important that the field types of the fields are identical. For example, the fields that two tables have in common cannot be a text field on the first table and a number field in the second table. Oddly enough, the two fields need not have the same name. However, you will find that it is much easier if the common field names are identical. This is just a matter of convenience for you, not a requirement in Access 2007.

Once your two tables contain a common field, you still need to create the relationship between the data tables. In other words, simply having two tables share a common field is not enough to establish a relationship between the tables. Access 2007 does not assume a relationship between tables simply because two tables contain the same data in the two fields.

The field you wish to use to link two tables is likely the primary key on the one table but is not often the primary key of the other. For example, the primary key that uniquely identifies customers in a “customers” data table would not be used to uniquely identify the data in a “products” data table.

There are three types of relationship you can create between tables in an Access 2007 database. Each of these relationship types carries with it specific advantages over the other types. A little planning ahead of time before you begin creating your database can help you determine the types of relationships you are likely to create later, saving you time and effort down the road.

The most common relationship type in Access 2007 is the one-to-many relationship. One-to-many relationships associate one record in one table with many records in another table. For example, suppose you have one table that contains customer information and one table that lists each of the products you sell on your website. Now, suppose you want to track which products each of your customers have ordered. Since it is possible that each individual customer has ordered more than one product, you need a one-to-many relationship between the tables to associate a customer (one) with the products (many) he/she has ordered.

One-to-one relationships are rare in an Access 2007 database because two tables with a one-to-one relationship can be combined into one table. For example, suppose you have a database with customer names in one table and customer addresses in another table. Unless you have a significant number of customers that share the same address, there is no reason to create a one-to-one relationship between the two tables to link each customer with his/her unique address. Simply combine the tables into one table with a “customer” field and an “address” field.

Access 2007 introduced the ability to create a many-to-many relationship between tables. In previous versions of Access, many-to-many relationships were not directly allowed but were possible with the use of a junction table. A junction table was a third table that has a one-to-many relationship with each of the first two tables. The result was an effective many-to-many relationship between the first two tables.

As its name implies, a many-to-many relationship allows you to link many records in one table to many records of another. For example, you may be interested to know what products (many) a customer (one) has ordered in the past. However, you may also be interested in what product (one) has been ordered by each of your customers (many). Creating a many-to-many relationship between a “customers” table and a “products” table would allow you to identify both of these conditions.

Access 2007 still allows you to use junction tables if you prefer. This is especially helpful if you are upgrading a database from a previous version of Access to the 2007 version. However, a new function in Access 2007 allows you to create a many-to-many relationship without a junction table. Multivalue fields can replace the use of junction tables when creating a many-to-many relationship. Multivalue fields allow you to store more than one data item in a field. The result is a many-to-many relationship that can be a powerful tool that reduces redundancy and the size of your database at the same time.

You can create three types of relationships in an Access 2007 database. The most common is the one-to-many relationship that relates one record in the first table to many records in the second table. One-to-one relationships are rare because you can normally collapse the two tables into one reducing the complexity of creating and maintaining an unnecessary relationship. Creating many-to-many relationship no longer requires a junction table as it did in previous versions of Access. However, junction tables are still possible in Access 2007, which can be an important consideration if you are upgrading an Access database to the 2007 version.