title: Count Distinct in a Group By aggregate function in Access 2007 SQL
body: <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>SELECT DiagCode, Count(Distinct(CustomerID))
  FROM CustomerTable
  Group By DiagCode;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>SELECT DiagCode, Count(CustomerID)
  FROM CustomerTable
  WHERE CustomerID in (SELECT Distinct CustomerID from CustomerTable)
  Group By DiagCode;
  </code></pre>
  
  <p>Hope I'm being clear here like I said my first post and any help is appreciated.</p>