CODEWITHSUNDEEP
sqlms-access
Luke
Luke

Reputation: 3

SELECT Query in ms access

I have an ms access table like the following:

UserName | PC_Type

Rob      | Desktop
Frank    | Laptop
Rob      | Laptop
Lindesy  | ThinCient
Mark     | Desktop
Paul     | Desktop
Paul     | ThinClient

How can I create a SELECT query that returns only users who doesn't have neither ThinClient nor Laptop?
I tried: 

SELECT DISTINCT UserName, PC_Type 
From Table 
GROUP BY UserName, PC_Type
HAVING PC_Type <> "ThinClient" AND PC_Type <> "Laptop"  
[or where PC_Type Not In ("ThinClient", "Laptop")]

but this doesn't filter users excluding who has already a Laptop (or ThinClient) because it returns for instance "ROB | Desktop" who has already a laptop (or "Paul | Desktop" who has already a ThinClient). I tried queries to find duplicates, difference queries, crossqueries, but I'm starting to get confused. I was trying to create a subquery or a partition query but I don't know if I'm doing something right.

Anyone can help me with this, please ? I'd expect from the query to return only Mark.

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

You can use aggregation and HAVING:

SELECT UserName
From Table 
GROUP BY UserName
HAVING SUM(IIF(PC_Type IN ("ThinClient", "Laptop"), 1, 0) = 0 ;

Upvotes: 2

Related Questions