Replacement for a Count Distinct in Access SQL (Sub-query)

[SamScib]

Hello,

I have a table structured in the following way:

Part COO
1a1 US
1a1 CN
1a1 MX
1b1 US
1c1 US
1c2 US
1c3 US
1d1 MX
1d2 US

I would like to do a Count Distinct on the COO column grouped by Part to return only those parts that have more than 1 COO. In the above example, I would return part 1a1 with a count of "3" and 1d1 with a count of "2".

Can someone please give me the syntax to make this work using a sub-query in Access SQL?

Thanks a lot!

Sam

 

[Andrzejek]

Could you show some (or just one) attempt to solve your problem?

Have fun.

---- Andy

 

[BigRed1212]

This is the third thread by this poster on this subject with this data. It might be productive for potential responders to review the previous threads and for OP to clarify the ultimate goal instead of piecmealing the way there.

 

[SamScib]

SELECT t.[material #], Count(*) AS COO_Count
FROM (SELECT DISTINCT origin, [material #] FROM [COO Initial] WHERE origin <> "" GROUP BY origin, [material #]) AS t
GROUP BY t.[material #];

I have tried to accomplish the same goal in many ways, by structuring my table differently, and nothing has worked until this. This will be the last post on this specific problem!