How to do Grouping with Select Distinct

[accessmatt]

Hello,

I found a good tip about how to get distinct from a field in Access here (http://stackoverflow.com/questions/1...n-access-query) but could someone help me modify that so that I can see distinct by FIELD1, FIELD2 and FIELD3. I tried Group By in Access but that wasn't giving me what I need.

SELECT Count(*) AS [Distinct FIELD1]
FROM (SELECT DISTINCT FIELD1 FROM data) AS [%$##@_Alias];

Thanks!

[June7]

Maybe:
SELECT
(SELECT Count(*) AS [F1] FROM (SELECT DISTINCT FIELD1 FROM data)) AS CountDistinctF1,
(SELECT Count(*) AS [F2] FROM (SELECT DISTINCT FIELD2 FROM data)) AS CountDistinctF2,
(SELECT Count(*) AS [F3] FROM (SELECT DISTINCT FIELD3 FROM data)) AS CountDistinctF3
FROM data;

[accessmatt]

JUNE7, that got me closer but what happened was that it just counted the distinct of those three fields. Really I just want to count the distinct of Field 1 but show "Grouped By" Field2 and Field3. Something line this

Field 1: 27 (count of all instances APPLES AND JANUARY)
Field 2: Apples
Field 3: January

does that make sense?

[June7]

Okay, try:
SELECT Count(Field1) AS CountF1, Field2, Field3 FROM (SELECT Field1, Field2, Field3 FROM tablename GROUP BY Field1, Field2, Field3) AS Q1 GROUP BY Field2, Field3;

What is Field1 data?

[accessmatt]

WoW, June7 - A+ for that! I wish I knew what was going on here but no matter, it does work! Field 1 data is a USER ID.

[June7]

Inner query retruns unique combinations of UserID with Field2 and Field3. The outer query then counts how many UserIDs for each combination of Field2 and Field3.

If it helps to understand, could build two query objects. First build the inner query then build another query that references the first.