Count Distinct in a Group By aggregate function in Access 2007 SQL

Question

Hello I have browsed the forum for a while and am asking my first question here. I'm in a bit of a bind and was wondering if I could get some help out. I am using Access 2007 and have not found a good answer to the question on the Net yet.

My data is Diagnostic Codes and CustomerID's and what I am looking for is a why to find the distinct count of CustomerID's for each Diagnostic Code. Ideally in non-Access SQL it would look like this:

SELECT DiagCode, Count(Distinct(CustomerID))
FROM CustomerTable
Group By DiagCode;

I know this is a pretty straightforward question but the answers that I'm finding are either too complicated(multiple aggregate functions) or too simple. Here is an approach I made to solving it but this is returning too many results:

SELECT DiagCode, Count(CustomerID)
FROM CustomerTable
WHERE CustomerID in (SELECT Distinct CustomerID from CustomerTable)
Group By DiagCode;

Hope I'm being clear here like I said my first post and any help is appreciated.

Answer 1

I'm not expert in MS Access and it is quite a long time last time I have written anything for it, but this maybe will work:

SELECT cd.DiagCode, Count(cd.CustomerID)
FROM (select distinct DiagCode, CustomerID from CustomerTable) as cd 
Group By cd.DiagCode;