Show records that don't match 1

[din2005]

Hi all i have this query which checks the hospno and treatdate and shows all records that match.

the sql is this:

SELECT tExternal.HospNo, tExternal.trtDate
FROM tblTreatmentConv RIGHT JOIN tExternal ON (tblTreatmentConv.TreatConversion = tExternal.trtDate) AND (tblTreatmentConv.HOSP_NO = tExternal.HospNo);

How do you create a query to show records that don't match? I think may be something easy but can't think of how to do it. Any advice will be much appreciated

Many thanks

 

[PHV]

Something like this ?
WHERE tblTreatmentConv.HOSP_NO Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

thanks again PHV

I don't think i got my query fully right i get a type mismatch error.

The sql is

SELECT tblTreatmentConv.HOSP_NO, tblTreatmentConv.TreatConversion
FROM tblTreatmentConv RIGHT JOIN tExternal ON (tblTreatmentConv.TreatConversion = tExternal.TreatIDImage) AND (tblTreatmentConv.HOSP_NO = tExternal.HospNo)
WHERE (((tblTreatmentConv.HOSP_NO) Is Null));

Could you find anything wrong with this

 

[PHV]

You want the records from WHICH table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[din2005]

basically the table texternal will have records that don't match the treatment table.

So i assume its tExternal


thanks

 

[PHV]

SELECT tExternal.HospNo, tExternal.trtDate
FROM tblTreatmentConv RIGHT JOIN tExternal ON (tblTreatmentConv.TreatConversion = tExternal.trtDate) AND (tblTreatmentConv.HOSP_NO = tExternal.HospNo)
WHERE tblTreatmentConv.HOSP_NO Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886