How do I count three different distinct values and group on an ID in MS-Access?
<p>So I know MS-Access does not allow <code>SELECT COUNT(DISTINCT....) FROM ...</code>, but I am trying to find a more viable alternative to the usual standard of</p>

<pre><code>SELECT COUNT(*) FROM (SELECT DISTINCT Name FROM table1)
</code></pre>

<p>My problem is I am trying to do three separate Count functions and group them on ID. If I use the method above, it is giving me the total unique value count for the whole table instead of the total count for only the value of ID. I tried doing</p>

<pre><code>(SELECT COUNT(*) FROM (SELECT DISTINCT Name FROM table1 as T2
WHERE T2.ColumnA = T1.ColumnA)) As MyVal
FROM table1 as T1
</code></pre>

<p>but it tells me I need to specify a value for T1.ColumnA.</p>

<p>The SQL query I am trying to accomplish is this:</p>

<pre><code>SELECT ID
COUNT(DISTINCT ColumnA) as CA,
COUNT(DISTINCT ColumnB) as CB,
COUNT(DISTINCT ColumnC) as CC
FROM table1
GROUP BY ID
</code></pre>

<p>Any ideas?</p>