Unmatching query Wizard

[morlan]

I am trying to find records in table A (tblMain) that dont match records in table B (tblAR)
I am using the Unmatching Query Wizard in Access to try and find records that dont match by Surname but I also want it to find records that dont match by DateOfBirth aswell. Here is the query:

SELECT tblMain.ID, tblMain.AppDate, tblMain.SURNAME, tblMain.CustDOB
FROM tblMain LEFT JOIN tblAR ON tblMain.SURNAME= tblAR.SURNAME
WHERE (((tblAR.SURNAME) Is Null));


Basically, I want the query to return records where " John born on 01/01/1950 " was not found in tblAR.


I've tried:

SELECT tblMain.ID, tblMain.AppDate, tblMain.SURNAME, tblMain.CustDOB
FROM tblMain LEFT JOIN tblAR ON (tblMain.SURNAME= tblAR.SURNAME) and (tblMain.CustDOB = tblAR.CustDOB)
WHERE (((tblAR.SURNAME AND tblAR.CustDOB) Is Null));

This does't return the desired results.

What am I doing wrong?

 

[morlan]

Hi,

The above query only brings back records where the CustName and CustDOB fields are blank in tblAR. This is not the result I'm after.

Here are two sample tables:

tblMain

ID - Name - DOB

1 - Brady - 01/01/1950
2 - Lewis - 04/01/1960
3 - Davis - 08/03/1980


tblAR

ID - Name - DOB

1 - Bradt - 01/01/1950
2 - Lewis - 04/01/1960
3 - Davis - 08/03/1980
4 - Doe - 03/07/1960

I want the query to return "1 - Bradt - 01/01/1950" because the surname has been mis spelt. It should also return "4 - Doe - 03/07/1960" as this record is not contained in tblMain.

Maybe I shouldn't be using "IsNull"

Can you help? Thanks