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 a third for the corresponding letter grade.

Figure 1-27. The two sample tables, tblGrades and tblLookup

Your goal is to create a query listing each student along with his letter grade. To accomplish this goal, follow these steps:

1. Create a new query including both the sample tables. Don’t attempt to use the standard Access methods to create a join between the tables, because there’s no mechanism for creating the kind of join you need.

2. Drag the fields you’d like to include in your query to the query grid. Make sure to include the field that will link the two tables together (Grade, from tblGrades, in this case).

3. In the Criteria cell for the linking field, enter the expression you’ll use to link the two tables, using the following syntax for any fields in the second table:

                        TableName.FieldName

   Because you have not related the two tables, Access needs the table name to know what you’re referring to. In the sample, the expression is:

   Between [tblLookup].[LowGrade] And [tblLookup].[HighGrade]

   Your finished query should resemble Figure 1-28.

   

   Figure 1-28. The sample query, qryGrades, in design mode

4. Run the query. The output should look something like Figure 1-29. For each numeric grade, you have related the data in tblGrades to the values in tblLookup, matching one row in tblLookup to each numeric grade.

   

   Figure 1-29. Data returned by qryGrades

Discussion

In a normal join relating two tables, Access takes each value in the lefthand table (imagine the two tables laid out in the query design, one on the left and one on the right), finds the first matching value in the related field in the righthand table, and creates a new row in the output set of rows containing information from the two joined rows. In this case, however, you want to match the two tables not on equality, but rather on “betweenness.” Access doesn’t graphically support this type of join in query design view, but you can get the same result by specifying that you want values for the linking field in the lefthand table only when they are between the two comparison values in the righthand table. As it builds the output set of rows, Access looks up each value of the linking field in the righthand table, searching for the first match. It joins the rows in the two tables based on the value from the lefthand table being between the two values in the righthand table.

All queries in Access are converted to SQL. If you select View → SQL or use the SQL icon on the toolbar, you can view the SQL for the qryGrades query. When you do, you’ll see the following SQL:

SELECT tblGrades.Name, tblGrades.Grade, 
tblLookup.LetterGrade
FROM tblGrades, tblLookup
WHERE (((tblGrades.Grade) Between [tblLookup].[LowGrade] 
And [tblLookup].[HighGrade]));

The inequality join has been translated into the WHERE clause of Access SQL. If you’re familiar with Access SQL, however, you may notice that the join information is not where Access normally places it. For example, if we had created a “normal” equijoin between these two tables, joining Grade from tblGrades to LowGrade in tblLookup, the SQL would look like this:

SELECT tblGrades.Name, tblGrades.Grade, 
tblLookup.LetterGrade
FROM tblGrades INNER JOIN tblLookup 
ON tblGrades.Grade = tblLookup.LowGrade;

This query will not give us the desired result. Notice that Access has placed the join information in the FROM clause. (The joining of tables in the FROM clause was introduced in the ANSI 92 SQL standard, but Access also supports joins in the WHERE clause, which is ANSI 89 SQL compatible.) It’s interesting to note that you can run queries converted from older versions of Access that specify non-equijoins using the FROM clause syntax, but you can’t create new queries with this syntax. qryScoresSQL in the sample database runs fine, and you can view the following syntax in SQL view:

SELECT DISTINCTROW tblGrades.Name, tblGrades.Grade, tblLookup.LetterGrade
FROM tblGrades INNER JOIN tblLookup ON tblGrades.Grade 
BETWEEN tblLookup.LowGrade AND tblLookup.HighGrade

However, if you copy this SQL and paste it into the SQL View pane of a new query, you’ll find that Access will report a syntax error and won’t let you save it. So, if you need to create non-equijoins, just stick to using the WHERE clause to define them.

This technique isn’t limited to the Between operator. You can use any comparison operator (Between, In, >, <, >=, <=, or <>) to perform a search in the second table, finding the first row that meets the required criterion. You can even link two tables using the InStr function (which indicates if and where one string occurs within another) to match words in a column of the first table with messages that contain that word in the second table.

As with any relationship between two tables, you’ll get the best performance if the values in the matching fields in the righthand table are indexed. This won’t always help (using InStr, for instance, there’s really no way for an index to help Access find matches within a string), but in many cases it will. Consider indexing any fields used in the matching condition in either of the tables involved in your relationships, whether you build them yourself or use Access’s primary key indexes.

Tip

The recordset produced by a query containing a non-equijoin will be read-only.