Access Cookbook - Ken Getz [25]
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.
1.11. Create a Query to Combine Data from Two Tables with Similar Structures
Problem
You have two tables of addresses, one for clients and one for leads. Generally you send different mailings to these two groups, but sometimes you need to send the same letter to both. You can always create a third table and append to it the data from each of the two tables, but there must be an easier way that doesn't involve the use of temporary tables. Is there a way to combine the data from these two tables into a single recordset, including only the U.S. addresses and sorted by zip code?
Solution
Access provides a special type of query that you can use to vertically splice together the data from two or more tables. The tables don't even need to have the same fields or fields of exactly the same data types. This is the union query, which can be constructed only by using the SQL View pane in the query designer.
The following steps show you how to construct a union query to combine data from two tables into a single recordset, limited to addresses in the U.S. and sorted by zip code:
Open 01-11.MDB. Open the two tables (tblClients and tblLeads) and examine their structure and data.
Create a new select query. Click on Close when you are prompted to add a table.
Select Query → SQL Specific → Union. Access will present a blank SQL view.
If you'd like, open tblClients in design view so you can see the field names while typing. Then type in the first part of the query:
SELECT Company, Address1, Address2, Address3, City, StateProvince, ZipPostalCode,