Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 1.10 Create a Join That's Based on a Comparison Other than Equality

1.10.1 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.

1.10.2 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 screenthe table
paneby 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:

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

1.10.3 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.

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