find entry where id doesn't have a certain entry in another field

[Guest]

I need help with a query. In a table each id can have more than one
output_device, but must have at least an entry for 'RECS'. How can I identify
id's that don't have an entry where output_device = 'RECS'? Example, id of
567 is ok, but I want to get id 459 because it doesn't have an entry for RECS:

id output_device
567 RECS
567 IA3
459 IM7
459 TR4

Thanks for any ideas!

 

[Jason Lepack]

SELECT DISTINCT
A.id
FROM
[your_table] AS A
LEFT JOIN (
SELECT DISTINCT id
FROM [your_table]
WHERE output_device = 'RECS') AS B
ON A.id = B.id
WHERE B.id is null

Change [your_table] to the name of your table and paste this in SQL
View of the query designer.

Cheers,
Jason Lepack

 

[Guest]

Try this --
SELECT denise.ID
FROM denise
WHERE (((denise.output_device)="RECS"));

SELECT denise.ID
FROM denise LEFT JOIN denise_1 ON denise.ID = denise_1.ID
GROUP BY denise.ID, denise_1.ID
HAVING (((denise_1.ID) Is Null));

 

[John W. Vinson]

I need help with a query. In a table each id can have more than one
output_device, but must have at least an entry for 'RECS'. How can I identify
id's that don't have an entry where output_device = 'RECS'? Example, id of
567 is ok, but I want to get id 459 because it doesn't have an entry for RECS:

id output_device
567 RECS
567 IA3
459 IM7
459 TR4

Thanks for any ideas!

A NOT EXISTS clause will work:

SELECT ID FROM yourtable
WHERE NOT EXISTS(SELECT ID FROM yourtable AS X
WHERE X.ID = yourtable.ID AND X.output_device = "RECS");

John W. Vinson [MVP]

 

[Guest]

Thanks for all the responses. They all worked for what I needed. I like the
distinct way best.