Distinct Aggregate Query in Microsoft Access with Group By on a Different Field

Question

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 http://blogs.office.com/b/microsoft-access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx and none seem to address this exact issue. Here's what I tried:

A)

SELECT PO_NUM, Count(CTN_NUM) AS CountOfCTN_NUM  
FROM tempSpring_ASN  
GROUP BY PO_NUM;  

This returns the count of ALL CTN_NUMs per PO_NUM, even if they are not unique.

B)

SELECT PO_NUM, Count(DISTINCT CTN_NUM) AS CountOfCTN_NUM  
FROM tempSpring_ASN  
GROUP BY PO_NUM;  

While this may work in other RDBMS’s, in Access I get a syntax error.

C)

SELECT COUNT(*)  
FROM  
(SELECT DISTINCT CTN_NUM AS cn  
FROM tempSpring_ASN);  

This returns the sum of CTN_NUM’s which are distinct to the table, not distinct to the PO_NUM.

D) Same as C, but with a GROUP BY:

SELECT COUNT(*)  
FROM  
(SELECT DISTINCT CTN_NUM AS cn  
FROM tempSpring_ASN)  
GROUP BY PO_NUM;  

This prompts me for the PO_NUM.

Can you please advise? Thanks.

Answer 1

Try this one:

SELECT PO_NUM, COUNT(*) AS CountOfCTN_NUM    
FROM   (
    SELECT PO_NUM, CTN_NUM 
    FROM tempSpring_ASN
    GROUP BY PO_NUM, CTN_NUM 
) a
GROUP BY PO_NUM;   

This should also work:

SELECT PO_NUM, COUNT(*) AS CountOfCTN_NUM   
FROM   (
    SELECT DISTINCT PO_NUM, CTN_NUM 
    FROM tempSpring_ASN
) a
GROUP BY PO_NUM;   

Answer 2

Letter D has a syntax error on it. PO_NUM is not present on subquery so you can't use group by on it. It might be that the subquery misses PO_NUM.