I had the same question and found a link (now defunct) in Access Team at Microsoft to have a good working example of how to do this; which I will also include here below.
Data:
Color Value Red 5 Green 2 Blue 8 Orange 1 Red 8 Green 6 Blue 2
To get the number of unique colors in a table, you can write a query, for example:
SELECT Count(Distinct Color) AS N FROM tblColors
This will return a value of 4 because there are four unique colors in the Color field in the table. Unfortunately, the Database Database Engine does not support the Count (Distinct) aggregate. To return this value from the Access table, you will need to use a subquery, for example:
SELECT Count(*) AS N FROM (SELECT DISTINCT Color FROM tblColors) AS T;
Now let me say that you also want to include another aggregate value, such as Sum, and want to group by some value, in this case Color. On SQL Server, you can write this query as:
SELECT Color, Sum(Value) AS Total, Count(Distinct Color) AS N FROM tblColors GROUP BY Color
This gives the following results:
Data:
Color Total N Blue 10 1 Green 8 1 Orange 1 1 Red 13 1
Now, if you ask whether to return a value of "1", the answer is yes. As I understand it, the graph (Distinct) here can be used as a test to check the results of this query.
If your data is on a server that supports Count (Distinct), you can use an end-to-end query to get the results. If you work with Access data, this gets a little trickier.
Since we used subqueries for the previous query, we will need to do the same here. However, the trick is that we need to use two subqueries, as shown in the following SQL:
SELECT C.Color, Sum(C.Value) AS Total, T2.N FROM (SELECT T.Color, Count(T.Color) AS N FROM (SELECT DISTINCT Color, Count(*) AS N FROM tblColors GROUP BY Color) AS T GROUP BY T.Color) AS T2 INNER JOIN tblColors AS C ON T2.Color = C.Color GROUP BY C.Color, T2.N;