Records in one table not in another 1

[TrekBiker]

I'm wanting to extract records in the Comments Written table below that don't have matching WrittenID in the query qryWrittenWithMatchingPresentation.

This Select statement produces no results

SELECT [Comments Written].EntryID, [Comments Written].Judge, [Comments Written].Category, [Comments Written].Company
FROM [Comments Written]
WHERE ((([Comments Written].[WrittenID]) Not In (Select WrittenID FROM qryWrittenWithMatchingPresentation)));

I want it to show the fields for WrittenIDs 1, 2, 3, 5, 8 9 etc.

Guidance appreciated, as ever.

 

[Andrzejek]

It should work, right?

Do you get the correct ID's when you just run the 'inner' Select?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TrekBiker]

Thanks Andy, your suggestion gave me confidence I was on the right track, so checked, cleared open queries etc and now have this SQL, which works!

SELECT [Comments Written].WrittenID, [Comments Written].EntryID, [Comments Written].Judge, [Comments Written].Category, [Comments Written].Company
FROM [Comments Written]
WHERE ((([Comments Written].[WrittenID]) Not In (Select WrittenID FROM qryWrittenWithMatchingPresentation)));

The vital thing I think was adding [Comments Written].WrittenID in the first Select.

Now get this, which is right.

Many thanks

 

[Andrzejek]

adding [Comments Written].WrittenID in the first Select

That should NOT be creating your original issue. Something else was going on, IMO

BTW - your statement could be written a lot shorter:[blue]
[tt]SELECT WrittenID, EntryID, Judge, Category, Company
FROM [Comments Written]
WHERE WrittenID Not In
(Select WrittenID FROM qryWrittenWithMatchingPresentation)
[/tt][/blue]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson