Multitable Queries

Until now, all the queries you've done in this book have been based on one table. That's fine for learning some basics and a few features and techniques. But it isn't why you're using Access or why you bought this book. All the hard work of organizing data into tables and creating relationships starts to pay off when you create queries using more than one table.

As I've emphasized often, the way you organize data into tables has little to do with how you actually use that data. You organized your database for maximum efficiency and speed, without worrying about the final purposes to which its data will be put.

Specifically, you don't care that customer names are not in the Orders table, even though they're needed for the order form you want to create. You're indifferent that supplier phone numbers are absent from the Products table, even though they're required for the inventory reports you need to build. You remain serene in the face of an Orders table with no mention of products purchased and in what quantities because you know you can easily bring both orders and order details together in one place when you need them.

That place is a multitable query.

Multitable Queries with Two Tables

Let's first look at a multitable query with two tables. In Chapter 5, I argued that lookup fields in tables were unnecessary. The example I used centered on the SupplierID field in the Suppliers and Products (or Merchandise) tables. I'm going to restate the problem the SupplierID field presents and then show you how a multitable query solves it.

1.	In the Database window, open the tblSuppliers table. Review the data and note that it contains only information about suppliers.
2.	Click View to switch to Design view. The primary key is SupplierID, which uniquely identifies each supplier in the table. Its data type is AutoNumber.
3.	Close the Suppliers table. Open the tblMerchandise table and review its data. Look at the Supplier column. The IDs refer to companies in the Suppliers table. Simply as IDs, however, they are cryptic and mystifying. Which companies do these IDs refer to? Close the Merchandise table.
4.	Choose Tools, Relationships to open the Relationships window. Locate the Suppliers and Merchandise field lists. To refresh your memory, the heavy line between the field lists indicates that the two tables have a one-to-many relationship and that referential integrity has been enforced. The field through which they are related is the SupplierID field, which has matching data. The 1 indicates that each supplier can appear only once in the Supplier table; the infinity sign indicates that a single supplier can appear any number of times in the Merchandise table.
5.	Close the Relationships window. The Suppliers and Merchandise tables have a one-to-many relationship, which ensures that you can create a query that will bring together data from both tables and show the name of the supplier that makes each product.

NOTE

Shortly, I'll mention why you don't need to have a relationship to bring together data from two tables. But if you do have one, you're golden.

Let's create a query that shows not only the supplier for each product, but other contact info as well.

1.	In the Database window, click Queries and click New. With Design View chosen in the New Query dialog box, click OK.
2.	In the Show Table dialog box, select tblSuppliers, press Ctrl, select tblMerchandise, click Add, and click Close. Maximize the Query Design window. In the query, notice that the line between the two field lists is the same as it was in the Relationships window. Because the two fields have matching names and data, you can create a query using fields from both tables. Access retrieves records where the fields from both tables are equalthat is, where they have matching data. NOTE It makes no difference that the Merchandise field list is on the left and the Suppliers field list is on the right. If you want to put the lists "in order," click the Merchandise title and drag it to the right of Suppliers.
3.	In the Merchandise field list, double-click MerchName to add it to the design grid.
4.	From the Suppliers field list, double-click SuppCompanyName, SuppContFirstName, SuppContLastName, SuppContTitle, and SuppPhone. Note that there isn't any need to include the SupplierID field from either field list; having the relationship in place is all you need.
5.	Save the query as qrySupplierContactInfo.
6.	Click View to see the records. Here's all the information you need, but it's not in any meaningful order. Let's sort the data by product name.
7.	Click View to return to Design view. In the MerchName column, type a in the Sort row for an ascending sort.
8.	Click View to see the records. Click View to switch to Design view, which should look like Figure 8.6. Save your changes. Figure 8.6. The multitable query shows the relationships between field lists.

Multitable Queries Using Three Tables

Suppose you'd like to add category descriptions for each product to the query. That data is not in the Suppliers or Merchandise tables, but it is in the Categories table.

1.	In the Database window, open the tblCategories table, review the data, and close the table.
2.	Choose Tools, Relationships to open the Relationships window. The Categories table has a one-to-many relationship with the Merchandise table through the CategoryID field. Because this relationship exists, you can add the Categories table to the query and include its data in your results.
3.	Close the Relationships window.
4.	In the Query Design window of qrySupplierContactInfo, click the Show Table button (refer to Figure 8.6 for its location) and add the tblCategories table. Each category can appear only once in the Categories table, but it can appear any number of times in the Merchandise table.
5.	Select CategoryDescription in the Categories field list and drop it in the SuppCompanyFirstName column (see Figure 8.7). Figure 8.7. A query using three tables. The Description field is now included in the query, next to the MerchName field.
6.	Click View to see the additional field.
7.	Save the query as qrySuppContDesc and close it.

Q&A

Q1:	What do I do if there isn't a field of matching data between two tables? Can I still create a multitable query, or am I out of luck?
A1:	You can use an intermediate table that has fields with matching data in both tables. When you think about it, that's really what you just did. You were able to include data from both the Suppliers and Categories table in the query, even though they don't have a common field, because the Merchandise table contained both the SupplierID and CategoryID. The next exercise provides a good example.

Multitable Queries with Criteria

Let's look at a slightly more complex example using criteria. Assume that Nifty Lions learns there are safety problems with some merchandise it handles and it has to contact customers who received those products. First, it needs to contact any customer who received items made by Feline Fantastics. Second, it needs to contact any customer who received products made by Ohio Traders in orders shipped before 8/15/04. (That's probably not the most realistic example, but it will provide a nice illustration.) Here's how to create a query that provides you with the information you need:

1.	In the Database window, click Queries and click New. With Design View chosen in the New Query dialog box, click OK.
2.	From the Show Table dialog box, add the tblCustomers, tblOrders, tblOrderDetails, tblMerchandise, and tblSuppliers field lists, in that order. Close the Show Table dialog box. Note that all the tables are tied together by one-to-many relationships. No fields are needed from the OrderDetails table, but it's necessary to tie together the Orders and Merchandise tables.
3.	Save the query as qrySafety.
4.	From tblCustomers, add the CustFirstName, CustLastName, and CustPhone fields.
5.	From tblOrders, add the OrderID and ShippedDate fields. From tblMerchandise, add MerchName. From tblSuppliers, add SuppCompanyName.
6.	On the Criteria row of the ShippedDate field, type <8/15/04. On the same row in the SuppCompanyName field, type ohio traders. As discussed in Chapter 7, both conditions must be true, so you're using AND criteria; this is entered on the same row.
7.	On the Or row of the SuppCompanyName, type feline fantastics. Only one condition need be true, so you're using OR criteria.
8.	Type a in the Sort row of the CustLastName field to sort the orders by customer name and click outside the first column. Your design grid should look like Figure 8.8. Figure 8.8. The query uses both AND and OR criteria, strategically placed on different rows.
9.	Click View to see the customers you need to contact and the products you need to inform them about.

Q&A

Q1:

I follow the logic of what you're saying about matching data. But to be honest, I don't have a gut feel for how it's used to retrieve these records. So many things are going on at once in this query; the records seem to come from every direction.

A1:

Maybe Figure 8.9 can help. I've tiled the five tables used in the query. I've also scrolled the records so you can follow how the values for the first record in the query, for Jessica Bernal, are retrieved. Figure 8.9. The matching data among the tables enables Access to retrieve the records you need. I'll start at the top of the first column: Bernal's customer ID is 19. Below in the Orders table, you can see that customer #19 made order #25. In the Order Details table, you see that order #25 includes item #15. Moving to the top of the second column, item #15 in the Merchandise table has supplier #1. Finally, in the Suppliers table, note that supplier #1 is Feline Fantastics. In the query, Feline Fantastics was OR criteria that was unrestricted by shipping date. So even though you can see in the Orders table that the order wasn't shipped until 8/31/04, it is still included in the records.