CODEWITHSUNDEEP
sqlms-access
B0WS3R
B0WS3R

Reputation: 15

COUNT DISTINCT MS ACCESS

I have a table called "orders"

clientID |  order_status
client1  | pending  
client1  | shipped
client2  | pending  
client2  | complete 
client3  | complete  
client3  | shipped
client4  | pending  
client4  | pending 
client5  | shipped  
client5  | shipped 
client6  | complete  
client6  | complete

I want to select any clientID that has records with order_status of both "pending" and "shipped"

This example should return only "client1"

Here is my query:

SELECT clientID  
FROM orders  
WHERE order_status IN("pending", "shipped")  
GROUP BY clientID  
HAVING COUNT(*) > 1

Which returns:

client1
client4
client5

I know I need to use COUNT (DISTINCT order_status) but this is a Microsoft Access Database and I'm not sure how to implement it properly

Upvotes: 1

Views: 9268

Answers (2)

xQbert
xQbert

Reputation: 35323

Generally handled by using a inline view in MSAccess. The inline view gets you the distinct order_status by client and then you can count.

SELECT clientID
FROM (SELECT ClientID, order_status 
      FROM orders 
      WHERE order_status IN("pending", "shipped")  
      GROUP BY clientID, order_status) B
GROUP BY ClientID
HAVING count(*) > 1

Or (because I don't like group by w/o aggregation)

SELECT clientID
FROM (SELECT Distinct ClientID, order_status 
      FROM orders 
      WHERE order_status IN("pending", "shipped")) B
GROUP BY ClientID
HAVING count(*) > 1

Upvotes: 1

ThinhNg
ThinhNg

Reputation: 9

Which your question

select any clientID that has records with order_status of both "pending" and "shipped"

I think the query statement should use and operator in where condition like that

SELECT clientID
FROM orders 
WHERE order_status = "pending" AND order_status = "shipped"

Upvotes: 0

Related Questions