Access Cookbook

Create a Join That’s Based on a Comparison Other than Equality

Problem

You need to join together two tables in a query on the Between operator. For example, you have a table of students and their grades, and a table of grade ranges and the matching letter grade. Though there are lots of ways to solve this problem with complex expressions and VBA, you know there must be a solution involving just queries. You need a way to join these two tables, finding matches when a value in the first table is between two values in the second table.

Solution

In Access, relationships between tables are normally based on equality, matching values in one table with those in another. Two tables in an Access query are normally joined in the upper half of the query design screen—the table pane—by dragging the join field from one table or query to the other. You can join tables this way for joins based on equality (“equijoins”) that can be inner or outer in nature.

Sometimes, though, you need to join two tables on some other relationship. However, Access doesn’t graphically support joins between tables that are based on an operator other than =. To perform these types of joins, you must specify the join in the criteria of the linking field.

From 01-09.MDB, open the tblGrades and tblLookup tables, both shown in Figure 1-27. The first table, tblGrades, includes a row for each student and the student’s numeric grade. The lookup table, tblLookup, contains two columns for the ranges of numeric grades and ...