Need help with a un-matched query 1

[DougP]

Need a Select statement that shows the missing [Removal Qty and Location].[Work Area] that is not in the [Worksite Checklist].[Work Area Num]
So find the same project number in both tables but show the one [Work Area] that is not in the [Worksite Checklist] table.
Example: there are 5 records in the [Worksite Checklist] table and only 4 of them are in the [Removal Qty and Location] table.
[Worksite Checklist] [Removal Qty and Location]
I I
II II
IV IV
V < need to find this record
III III

Here is what I started with

SELECT [Worksite Checklist].[Project Number], [Worksite Checklist].[Work Area Num], [Removal Qty and Location].[Work Area]
FROM [Worksite Checklist] INNER JOIN [Removal Qty and Location] ON [Worksite Checklist].[Project Number] = [Removal Qty and Location].[Project Number]
WHERE ((([Removal Qty and Location].[Work Area])<>[Worksite Checklist].[Work Area Num]));

TIA


DougP, MCP, A+

 

[PHV]

You wanted this ?
SELECT W.[Project Number], W.[Work Area Num], R.[Work Area]
FROM [Worksite Checklist] AS W LEFT JOIN [Removal Qty and Location] AS R ON W.[Project Number] = R.[Project Number] AND W.[Work Area Num] = R.[Work Area]
WHERE R.[Work Area] Is Null

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

 

[DougP]

yes,
HAVE YET ANOTHER STAR !!!!


DougP, MCP, A+