Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on updating 853 records regarding BA Majors. A good chunk have
been sporadically done according to a standard three letter code. I wish to
write a query that will pull up all the records EXCEPT for the 40 standard
three letter codes from my table.

How do I do this?
 
The query wizard about finding unmatched records could help, assuming the 40
codes are in a table (in 40 records).


SELECT a.*
FROM yourTable AS a LEFT JOIN codes
ON a.code = codes.code
WHERE codes.code IS NULL

Note that the WHERE condition is applied AFTER the JOIN is made. Clearly,
there is no NULL in codes.code itself, so, if some NULL appear, under that
column, after the join is made, it is because there was NO match. (Even if
there was a NULL in the original codes.code, the ON condition with a NULL =
NULL will NOT return TRUE, so it will be considered a no-match, as intended,
here, in this situation).

Hoping it may help,
Vanderghast, Access MVP
 
Michel said:
The query wizard about finding unmatched records could help, assuming the 40
codes are in a table (in 40 records).


SELECT a.*
FROM yourTable AS a LEFT JOIN codes
ON a.code = codes.code
WHERE codes.code IS NULL

Note that the WHERE condition is applied AFTER the JOIN is made. Clearly,
there is no NULL in codes.code itself, so, if some NULL appear, under that
column, after the join is made, it is because there was NO match. (Even if
there was a NULL in the original codes.code, the ON condition with a NULL =
NULL will NOT return TRUE, so it will be considered a no-match, as intended,
here, in this situation).

Hoping it may help,
Vanderghast, Access MVP
Click to expand...

If you're looking for ones that are not 3 characters in length, you
need to filter for it...
WHERE ...
AND Len$([Major])<>3
 
Back
Top