Access Cookbook - Ken Getz [24]
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-10.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:
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.
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).
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
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