Comparing a Table with a Query

[XP2000]

'TblMonth' has 2 fields: OrderRef, ProductNo
'QryOrders' has 2 fields: OrderRef, ProductNo

I need to determine the records that DO exist in 'TblMonth ' but that DO NOT exist in 'QryOrders'.
i.e.
Find the records where TblMonth.OrderRef matches QryOrders.OrderRef BUT subsequently there is no match between TblMonth.ProductNo and QryOrders.ProductNo


I just can't seem to get my head round this one.
Thanks.

 

[PHV]

A starting point:
SELECT M.*
FROM TblMonth M LEFT JOIN QryOrders O ON M.OrderRef=O.OrderRef AND M.ProductNo=O.ProductNo
WHERE O.ProductNo IsNull AND M.OrderRef In (SELECT DISTINCT OrderRef FROM QryOrders);

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

 

[XP2000]

Thank You....works a treat