Creating Multi-Table Queries, Advantages and Pitfalls

First, let's review our sample database which contains two tables: Students and Grades. We'll call students the primary table and grades the related table. Figure 1a and 1b show sample data from these tables.

Figure 1a

Figure 1b

Next we'll briefly examine a one-table query just to refresh our memories.

One-Table Queries

Using Access 2003, create a new query and add the Students table. Drag the fields StudentID, Lastname, Firstname, State, and Zip to the design grid, and then run the query using the exclamation point in the toolbar. You'll see the list of 197 students in the datasheet view. Next we'll add some criteria, example "PA" in the state column, and run the query again. See figure 2. This time only 117 students appear, only the PA students. So far so good. Next, we'll remove "PA" from the criteria and move on.

Figure 2

Two-Table Queries

Next, click the 'show table' icon in the toolbar and add the second table, Grades, to the query. See Fig 3.

Figure 3

Notice the join line between the two tables. This line appears if you have previously created a relationship between the two tables. If you run the query now, instead of 197 students, you will now see 44 students. See figure 4.

At first glance, the reason for fewer records may be intuitive: only students with grades made the list. However, upon closer examination, we see that the list also contains duplicates. For example, Ed Rosas appears twice. This is due to Mr. Rosas having two grades, perhaps for two different classes.

Figure 4

This brings us to the two potential Pitfalls when working with multi-table queries.

First Pitfall

If you attempt to list all the records in the primary table when you also have a second (related) table as part of your query, you may not see all the records in the primary table .

Second Pitfall

Under this same scenario, you may end up with duplicate records because there may be multiple records in the related table. In our sample database, a student may have more than one grade.

Resolve the Pitfalls

How can we avoid these pitfalls? The easiest way to avoid these two pitfalls is to remove the Grades table ( the related table ) from the query. Doing so will resolve both of these problems.

However, this solution will not always suffice. What if we want to select records based on a field in the Grades table? For example, if you want a list of students who received an "A." Under these conditions you can't remove the Grades table from the query.

To address this problem, let's return to the design view of our sample query. Verify that the Grades table is still in the query. See figure 3. Next, drag the Grade field form the Grades table to the query grid and type "A" in the criteria row. See Figure 5.

Figure 5

Run the query and this time you will see 24 students, those students who received an "A" in at least one class. Again, we have duplicates, for example Betty Rush. See Figure 6.

Figure 6

Let's deal with the duplication problem now. Return to the query design view. Find the Totals button on the toolbar and click it. See Figure 7.

Figure 7

Run the query again and now there are only 23 records in the datasheet view and only one record for Betty Rush. See Figure 8.

Figure 8

One other condition may arise when working with this type of query - what if you want to see all the students, not just those who received a grade? How can this be achieved with a multi-table query?

Again, let's start with the design view of our sample query. Remove the "A" criteria in the Grade column, but leave the Total row showing. Run the query and the result of this query will yield 43 records. Betty Rush only appears once, but Ed Rosas and others appear twice. See figure 9.

Figure 9

The Total row, with Group By in each column, will 'combine' duplicates but only if all the fields match. Since Ed Rosas has an "A" and a "B," both of his records are showing. But we don't want that. Let's suppose we only want the best grade (not both grades). To accomplish this, change the total row in the Grade column from 'Group By' to 'Min.' (Note: Min is needed since A is less than B in the order of the alphabet.) Re-run the query. See Figure 10.

Figure 10

Now each student has only one record. See figure 11. If a student has more than one grade, the query shows his or her best grade.

Figure 11

Finally, we need to show all the other students - those students without a grade. Perhaps the instructor needs to know which students he hasn't entered a grade for. To accomplish this task, return to the query design view. We will have to make several revisions. First, we need to change the join line between the tables. Right-click the line and choose "join properties." Click option two, "Include all records from Students." Click OK. See figure 12. This option will show us all the students even if they don't have a grade. See figure 13.

Figure 12

Figure 13

We have examined some of the pitfalls of multi-table queries. What about the advantages?

Advantages

You can turn the pitfalls into an advantage. For example, if you only want to see "only students who received a grade," you can add both the Students and Grades tables to a query, run the query, and you have the solution.

Or, what if you want to see "only students who did not receive a grade?" Just change the join property to 'include all records from students.' Then, enter is null into the criteria of the grade column. Run the query and you have the solution.

There you have it – a quick study of the pitfalls of multi-table queries and how to turn those pitfalls into an advantage.

You can create the queries as you follow along with this article by downloading the sample database. download sample data.