query not working

[samuelimtech]

Hi all.

I have the below query and its purpose is to look find if any record appears in table (sub_missingData) but not in another (Archive)

sub_missingdata Archive

JDE:,Week Ending JDE:,Week Ending
654321,04/07/2014 654321,04/07/2014
654321,11/07/2014
123456,04/07/2014
123456,11/07/2014



SELECT sub_MissingData.[Week Ending:], sub_MissingData.[JDE:]
FROM sub_MissingData LEFT JOIN Archive ON (sub_MissingData.[Week Ending:] = Archive.[Week Ending:]) AND (sub_MissingData.[JDE:] = Archive.[JDE:])
WHERE (((Archive.[JDE:]) Is Null));


the result should be the 3 bottom records from the sub_missingdata table but its returning all 4 records. thanks for any help.

[ranman256]

The left join WILL return all records. If you want ONLY records that equal, it must be an inner join.