Get rid of the record that matches

[zhuanyi]

Hello all,
Please refer to the previous thread where I was helped with the WHERE staement:
http://www.access-programmers.co.uk/forums/showthread.php?p=588055
Now, say for example, I have 2 records in the claimbase that has the same other fields but the claim_liability field have different values and another 2 matching records in AppendAllFields that has those 2 records as well (which is essentially the NAR field in AppendAllFields, if I run the inner join, what I will ended up getting is that those 2 records will be included because the system was trying to match one claim_liability to the other record's NAR, which I do not want that to happen because the purpose is to only brought up records that the amounts are agreed for more than 100 dollars.
May I know how should I change my program to suit my needs?
Thanks!

Code is attached here for your review:

SELECT [claimbase].[comp_code],
       [claimbase].[ced_comp_pol_num],
       [claimbase].[claim_liability],
       [claimbase].[date_of_death],
       [claimbase].[claim_status] 
INTO DeathRecon 
FROM [claimbase] Inner Join [AppendAllFields] On [claimbase].[ced_comp_pol_num] = [AppendAllFields].[POL] 
WHERE [claimbase].[comp_code] = 'SUN' OR 'CLA' AND <-- Change to In ('SUN', 'CLA')
[claimbase].[claim_status] = 'PAID' AND 
[claimbase].[date_of_death] >= #01/01/2003# AND 
[claimbase].[date_of_death] <= #01/06/2006# AND 
[claimbase].[claim_liability]  NOT BETWEEN ([AppendAllFields].[NAR]/100 + 100 ) AND ([AppendAllFields].[NAR]/100 - 100);