How to Code Select SubQuery for table lookup

[pvg]

Table #1 &quot;A&quot; (patient transaction records)<br>
Patient_name (30 Char)<br>
cpt_code 5 characters indexed dups allowed<br>
<br>
Table #2 &quot;B&quot; (master table of cpt codes)<br>
cpt_code 5 characters indexed no dups <br>
<br>
Objective of query in Access 2000<br>
Show patient transaction records where the cpt code in the &quot;A&quot; table is NOT found in the <br>
&quot;B&quot; table (master table of cpt codes)<br>
<br>
is this correct<br>
select * from A where A.cpt_code NOT IN<br>
(select B.cpt_code from B)<br>
<br>
Thanks for your help<br>
PVG

 

[carp]

Yes, this should work (although I'm not familiar w/ Access2000, this is pretty generic SQL).

 

[pvg]

To get the query to display ONLY the unmatched records, the solution is:<br>
<br>
Select A.cpt_code, A.Patient_name, B.cpt_code<br>
From A left join B on A.cpt_code = B.cpt_code<br>
Where (((A.cpt_code) NOT In (Select B.cpt_code from B)));

 

[carp]

The original query only displays unmatched records and will run faster.