James's Database
~~James La Borde

Joins and Relationships in Access

Hello Everyone,
This month I am going to provide you with part of my Intermediate level class for Access at Eclectic Academy, which I teach with my wife, Laura.

Throughout this lesson we will refer to the Northwinds sample database that Access provides. Please download it from http://office.microsoft.com/downloads/2000/Nwind2K.aspx if you do not already have it.

Let's jump right in and get started. We are going to start with Joins and Relationships.

Relationships:

Access is a relational database. What this boils down to is that you can store all of your data in several related tables. This reduces repetition and the potential for errors in entering the data. The relationship is the way one table or set of data is related to another table. Each table ideally has a primary key. This primary key is linked to a field in the related table so that the additional information from that table can be tied to it. The related key field is sometimes called a foreign key. The easiest way to remember the difference between the two is a primary key is unique to its table. A foreign key refers to a primary key in another table and may be duplicated.

Let's take a look at the relationship window of the Northwinds database. Once you have the database open, click on Tools, Relationships and it should bring up the screen you see below.

You will notice lines connecting fields on each line. These are the relationships we designed into our database. To create a simple relationship, click on a field in one table and drag it to its related field in another table. You should also notice symbols at each end of these relationships, these tell you what type of relationship these tables share. A number one means that it is a "one" side to a relationship. An infinity symbol represents the "Many" side of a relationship. There are three types of relationships:
 

One to One	Each field in table A has exactly one matching field in table B. Both fields must be indexed and are usually the primary key.
1----------------- ∞ One to Many	There is only one record in table A but each record can have many matching entries in table B. The Customers to Orders relationship is a One-to-Many relationship. One customer can have many orders. All of the relationships in the Northwinds database are of this type.
∞------- 1------- ∞ Many to Many	This is generally accomplished through two One to Many relationships going through a single One.

Joins:

Now that we have a better understanding of relationships, let's look at Joins. A join is the representation of a relationship in a query. It is the tool used to tell your query in what way you want your related data retrieved. There are three types of joins: Inner, Left Outer, and Right Outer. The first is the most common, the inner join. The Inner Join returns only data that matches in both tables. The Left Outer Join returns all data from the left table in the query window and only the matching data on the right table. The Right Outer Join does just the opposite. It returns all data from the right table and only matching data from that on the left.
Now, let's all take a crack at creating a join.

• First, open the Northwinds database.
• At the main Database window, click on the Queries Tab, then on New.
• Add the Customers and Orders tables to your design grid. Notice that there is a line between the two CustomerID fields. This is the join. If the fields are already joined in the Relationships Window, or if the fields are named the same and meet the criteria for one of the types of joins, the join line appears automatically.

This query will provide us with data where the CustomerID matches in both tables. Now let's take a hypothetical situation and adjust the query for it. The Marketing Director at Northwinds Trading Company has asked you to provide him a report of all customers and how many orders each placed in December of 1994.

• First, add the fields CompanyName, ContactName, Region, and OrderDate, by double clicking on each in the field lists at the top.
• To get the desired date range, enter 'Between 12/1/94 and 12/31/94' (without the single quotes) under the criteria for the OrderDate.
• Your query should now look like this:

Did you notice how Access automatically placed pound signs (#) around the dates? The pound sign is how Access recognizes the Date format, and it's a good way to know if you've entered a date properly.
This query will run just fine, but to make sure you're only getting the records the Marketing Director wants, you'll need to adjust the way Access thinks about the join. Double click the join itself (the line between the two fields). A new window will pop up with three options:

1. Only include rows where the joined fields from both tables are equal (This is the default option).
2. Include ALL records from "Customers" and only those records from "Orders" where the joined fields are equal. (Click this option)
3. Include ALL records from "Orders" and only those records from "Customers" where the joined fields are equal.

Congratulations! You have created an Outer Join (specifically, a Left Outer Join).

Referential Integrity:

According to the Access 97 help index, "Referential integrity is a system of rules that Microsoft Access uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data." This means that Access will help you enforce rules so that your data (a) is valid to start with and (b) remains valid throughout its usage.

Cascading Updates and Deletes:

Cascade Update Related Fields is an extremely useful option to use if your data - past, present, and future - relies on the most up to date information. A Cascade Update replicates your changes to data throughout your database. If you changed information in your primary table, Access would make that change in all of the related tables. Since this is a somewhat difficult concept, I will try to give you a business example. A Billing department needs the current address of all its customers regardless of where they lived when the product was shipped. The Billing department using Cascade Update has an advantage in that they only need to update the Billing Address in one place, and that change is reflected on all records, both past and future. There are also times where it's not a good idea to Cascade Update; for instance, in an employee table. If Joe Cool receives a salary increase and his commission percentage increases, you do not want to use Cascade Update because his commissions on past sales should remain unaffected by the change.

Cascade Delete Related Records can be dangerous and should be used with extreme caution. They do something similar to the Cascade Update. When you delete a record in a primary table, that deletion is carried out on all related or child tables as well. In some cases (a purge of outdated material, for instance) and this can be very helpful. It is also dangerous, as it can have disastrous outcomes. If you choose to use Cascade Delete for purging old records, it is recommended that you turn it on only long enough to complete your purge, then turn it off. Remember that in Access, once your data is gone, it is gone! The Undo option usually will provide you no solace and unlike Word and Excel, you cannot simply choose to not save your changes.

If you have any questions regarding this lesson, why not join our Access Intermediate class at Eclectic Academy?  We will be going into more detail on the multiple table queries.

James La Borde works in the computer department at a Credit Union, where he uses Access, SQL Server, VBA, and ODBC daily.  He also teaches online Access classes at Eclectic Academy.