The various tables of an Access database are bound together through a series of associations known as relationships. You can establish a relationship between two tables that have a common field with matching values. By far the most common and important of these associations is the one-to-many relationship. Each value can appear only once in the table on the one side of the relationship, but any number of times in the table on the many side of the relationship.

Relationships let you create queries that retrieve data from two or more tables. But what if there isn’t a one-to-many relationship between two tables that contains the information you want? Can you still retrieve the records you need? Let’s take a look at how to resolve this problem.

 Review Table Info

Open the Northwind.mdb in the Samples subfolder of your Microsoft Office folder. Assuming you have made a copy (or have a backup) of the Northwind database, open the file.

Suppose you’re Northwest’s owner. You’re reviewing the organization of the sales department. You’re not interested in which employee handled which order; that’s the job of sales management. But you would like to see a list of your customers and the job titles of the employees who’ve serviced their orders. We’ll use a multitable query to find this information.

In the Database window, click Tables and double-click the Employees table icon to open it. There are nine employees with four job titles; six are Sales Representatives. Notice that the table only has information about employees. You won’t find a field with data about customers. Close the Employees table.

Open the Customers table. In the Status Bar, you’ll see that there are 91 records, one for each customer. Review the fields and note that none has any info about employees. Close the table. Open the Orders table. Notice that each record includes the customer (naturally), as well as the employee responsible for the order. There are 830 orders. Close the Orders table.

 Create The Query

Now let’s create a query that will find the titles of the staff assigned to various customer orders. In the Database window, click the Queries button. Click New. In the New Query dialog box, select Design View if it’s not already. Click OK.

In the Show Table window, click Customers, press CTRL, and click Employees; click Add to add the two field lists to the Query window and click Close. Maximize the Query window.

From the Customers field list, double-click the CompanyName field to add it to the Design grid. From the Employees list, double-click the Title field to view the employee’s title.

In the Query window, you won’t see a relationship between the Customers and Employees tables. Moreover, as we saw, the two tables have no fields with matching data, so there is no way to create a link directly between the tables.

You want to find the job titles of the employees who have serviced particular customers. Under current conditions, let’s see which records Access retrieves. Click View and examine the results. Specifically, scroll through the records and review the Title field. Can you see a pattern?

Every one of the job titles for the nine employees are displayed in the same order for every customer. You can check that every record has been listed by doing the math: There are nine employees and 91 customers. Nine times 91 is 819, the number of records in this dynaset.

This result is called a Cartesian Product. When you’re lacking fields with common data with which to create an association between tables, Access includes all of the records from the selected fields from both tables.

As Northwest’s owner, these results are useless. You need to do more work to find the records you need.

 Include An Intermediary Table

Press F11 to return to the Database window. Choose Tools and then Relationships. Note that the Customers table has a one-to-many relationship with the Orders table through the CustomerID field. Also notice that the Employees table has a one-to-many relationship with the Orders table through the EmployeeID field.

Both Customers and Employees have one-to-many relationships with the Orders table. The two tables have no field in common with each other, but they do have relationships with a table common to both. Thus, through the Orders table, we can establish an association between the Customers and Employees tables.

On the Taskbar, click the Query button to return to your query. Click View to return to Design View. Choose Query and then Show Table. Select Orders, click Add, and click Close. Click the Title bar of the Employees field list and drag it to the right of the Customers field. (This last step changes nothing; it simply makes it easier to see the relationships.) In the CompanyName field, open the Sort drop-down field and click Ascending.

Click View. The customer name and employee title are listed for each order. There are 830 records, the total number of orders in the Orders table. Scroll down the list and you’ll see that all four sales titles are represented.

 Refine Your Results

This query tells you which job functions have been used to handle the orders of various customers, but it’s difficult to tell their distribution for a particular customer. Let’s add value to this query by summarizing and counting the orders.

Click View. From the menu bar, click View and Totals. Double-click OrderID in the Orders table to add it to the grid. In the OrderID column, click the drop-down list in the Total row and select Count. Click View. Now you can see the number of orders handled by each title for each customer.