Help writing Query

[tweety]

Hi,

I am new to access and am trying to create a query but are getting nowhere
fast, so hopefully someone can help me.
I have 2 tables (imported from excel), LTFS and Wcare. Both contain
lastname and dob fields ( and other fields but I only need to compare these 2)
..
I need a query that compares lastname and dob in both tables and returns the
ones that are missing from table LTFS.

eg

LTFS
Lastname DOB
Zhang 16/11/86
Zhang 6/7/88
Zhang 19/6/90
Zhang 13/7/87
Zhang 10/9/88
Zhang 2/9/89

Wcare
Lastname DOB
Zhang 30/12/87
Zhang 8/2/85
Zhang 13/7/87
Zhang 16/11/86

the result would be
Zhang 6/7/88
Zhang 19/6/90
Zhang 10/9/88
Zhang 2/9/89

which are all from the LTFS table ( I don't need the missing records in the
Wcare table)

Hope that makes sense!
Thanks in advance

 

[Rob Parker]

This will do it:

SELECT LTFS.LastName, LTFS.DOB
FROM LTFS LEFT JOIN Wcare ON LTFS.DOB = Wcare.DOB AND LTFS.LastName =
Wcare.LastName
WHERE Wcare.LastName Is Null AND Wcare.DOB Is Null;

The way to generate this query in the query design grid is to add both
tables, and join on the fields you want to compare. Then select each join
(by clicking on the join line), and change the join property to "Include all
records from LTFS and only those ...". Add the LastName and DOB fields from
both tables to the query, and set the criteria for the fields from the Wcare
table to "Is Null".

The "find Unmatched" query wizard will do most of this for you, but it's
limited to linking on a single field, rather than on multiple fields.

HTH,

Rob

 

[tweety]

Thank you!

You are an absolute legend!!

-tweety