Ask a new question

How to exclude specific data in a query

Hi.

I need to create a query based on a table where the computer id is the primary key, but i have to create a list of employee names who dont have a (location) home computer. My problem is some employees are listed twice if they have a home and work computer, so using NOT ‘home’ still has them appearing in my query, and google is giving me no love.

any help greatly apreciated

cheers

Answer
Answer
Restrict the query by means of a subquery which returns no rows where the value in the location column is 'Home', correlating the subquery with the outer query on the EmployeeID column or similar, e.g.

SELECT *
FROM YourTable as T1
WHERE NOT EXISTS
    (SELECT *
     FROM YourTable AS T2
     WHERE T2.EmployeeID = T1.EmployeeID
     And T2.Location = "Home");


________________________________
Ken Sheridan,
Newport, Shropshire, England

"Don't write it down until you understand it!" - Richard Feynman

3 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 14, 2025 Views 2,118 Applies to: