CODEWITHSUNDEEP
sqlms-access
Reijo
Reijo

Reputation: 31

How to count unique events in Access?

I am trying to count the unique number of events in the E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID field using count(distinct(E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)), but I get an error when performing the following query in MS Access 2016.

How to count unique events in the E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID field?

My query in MS Access 2016 (without distinct):

SELECT CUSTOMERS.E_CUST_LOGIN_ID, Count(E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)
FROM ((((CUSTOMERS 
INNER JOIN E_CUSTOMER_DETAIL ON CUSTOMERS.WH_CUST_NO = E_CUSTOMER_DETAIL.WH_CUST_NO) 
INNER JOIN E_CUST_E_CUST_REL ON CUSTOMERS.E_CUST_LOGIN_ID = E_CUST_E_CUST_REL.E_CUST_LOGIN_ID) 
INNER JOIN E_CUST_CHNL_USAGE_DETAIL ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUST_CHNL_USAGE_DETAIL.E_CUST_LOGIN_ID) 
INNER JOIN E_CUSTOMER_DETAIL AS E_CUSTOMER_DETAIL_1 ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)
GROUP BY CUSTOMERS.E_CUST_LOGIN_ID;

Upvotes: 1

Views: 52

Answers (1)

GMB
GMB

Reputation: 222402

MS Access does not support count(distinct). One way to work around this limitation is to select distinct in a subquery, then aggregate:

SELECT LOGIN_ID, Count(DETAIL_LOGIN_ID)
FROM (
    SELECT DISTINCT CUSTOMERS.E_CUST_LOGIN_ID AS LOGIN_ID, E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID AS DETAIL_LOGIN_ID
    FROM ((((CUSTOMERS 
    INNER JOIN E_CUSTOMER_DETAIL ON CUSTOMERS.WH_CUST_NO = E_CUSTOMER_DETAIL.WH_CUST_NO) 
    INNER JOIN E_CUST_E_CUST_REL ON CUSTOMERS.E_CUST_LOGIN_ID = E_CUST_E_CUST_REL.E_CUST_LOGIN_ID) 
    INNER JOIN E_CUST_CHNL_USAGE_DETAIL ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUST_CHNL_USAGE_DETAIL.E_CUST_LOGIN_ID) 
    INNER JOIN E_CUSTOMER_DETAIL AS E_CUSTOMER_DETAIL_1 ON E_CUST_E_CUST_REL.E_CUST_JOINT_OWN_LOGIN_ID = E_CUSTOMER_DETAIL_1.E_CUST_LOGIN_ID)

)
GROUP BY LOGIN_ID;

Upvotes: 1

Related Questions