MS Access Count Distinct Multiple Columns

Question

I have been trying to write a SQL based query utilizing Count distinct, but am running into issues in how to correctly structure the format for Microsoft Access.

My current query is in the following format, I would like to transform the Count operation into distinct count, if possible:

SELECT 
a.Customer_ID, a.First_Name,
COUNT (b.Payment_Method) AS Payment_Method <- would like to get the distinct count
Count (b.Store_Number) as Store_Number     <- would like to get the distinct count
SUM (b.Sales_Amount) AS Sales_Amount 
FROM Table1 a
INNER JOIN Table2 b ON a.Customer_ID = b.Customer_ID
WHERE (((a.Date_ID) > 1234))
GROUP BY a.Customer_ID, a.First_Name

I am familiar with the SQL construction of this query and was hoping to recreate the results using MS-Access.

Have to first build a query that returns the Distinct records. Then use that query in another query to count those records. — June7, Feb 23 '18 at 20:52
https://stackoverflow.com/questions/11880199/how-do-i-count-unique-items-in-field-in-access-query — xQbert, Feb 23 '18 at 21:04

score 1 · Accepted Answer · answered Feb 23 '18 at 21:01

This is much more complicated than in other databases, but you can before joining:

SELECT a.Customer_ID, a.First_Name, p.num_pm, s.num_sn, s.sum_sa
FROM (Table1 as a INNER JOIN
      (SELECT CustomerId, COUNT(*) as num_pm
       FROM (SELECT DISTINCT CustomerId, Payment_Method FROM Table2) as b
       GROUP BY CustomerId
      ) as p
      ON a.CustomerId = p.CustomerId
     ) INNER JOIN
     (SELECT CustomerId, COUNT(*) as num_sn, SUM(sa) as sum_sa
      FROM (SELECT CustomerId, Store_Number, SUM(Sales_Amount) as sum_sa
            FROM Table2
            GROUP BY CustomerId, Store_Number
           ) as b
      GROUP BY CustomerId
     ) as s
     ON s.CustomerId = a.CustomerId
WHERE a.Date_ID > 1234

MS Access Count Distinct Multiple Columns

1 Answers1