title: Distinct Aggregate Query in Microsoft Access with Group By on a Different Field
body: <p>Among the fields in my table are 2 fields, CTN_NUM and PO_NUM.  Each PO_NUM has at least one CTN_NUM, possibly more—distinct or repeated.  Any given CTN_NUM cannot have more than 1 PO_NUM.  In other words, it's a one to many relationship.  I want to create a query which shows the number of unique CTN_NUM's per PO_NUM.  I've seen other threads on this forum as well as <a href="http://blogs.office.com/b/microsoft-access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx" rel="nofollow">http://blogs.office.com/b/microsoft-access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx</a> and none seem to address this exact issue.
  Here's what I tried:</p>
  
  <p>A)  </p>
  
  <pre><code>SELECT PO_NUM, Count(CTN_NUM) AS CountOfCTN_NUM  
  FROM tempSpring_ASN  
  GROUP BY PO_NUM;  
  </code></pre>
  
  <p>This returns the count of ALL CTN_NUMs per PO_NUM, even if they are not unique.  </p>
  
  <p>B)  </p>
  
  <pre><code>SELECT PO_NUM, Count(DISTINCT CTN_NUM) AS CountOfCTN_NUM  
  FROM tempSpring_ASN  
  GROUP BY PO_NUM;  
  </code></pre>
  
  <p>While this may work in other RDBMS’s, in Access I get a syntax error.  </p>
  
  <p>C)  </p>
  
  <pre><code>SELECT COUNT(*)  
  FROM  
  (SELECT DISTINCT CTN_NUM AS cn  
  FROM tempSpring_ASN);  
  </code></pre>
  
  <p>This returns the sum of CTN_NUM’s which are distinct to the table, not distinct to the PO_NUM.  </p>
  
  <p>D) Same as C, but with a GROUP BY:  </p>
  
  <pre><code>SELECT COUNT(*)  
  FROM  
  (SELECT DISTINCT CTN_NUM AS cn  
  FROM tempSpring_ASN)  
  GROUP BY PO_NUM;  
  </code></pre>
  
  <p>This prompts me for the PO_NUM.  </p>
  
  <p>Can you please advise?  Thanks.  </p>