Aggregates / Counting distincts

[sohunter]

Greetings fellow Access-ers. I need to count distinct combinations, and am stuck on how to do it in a query.

Code: Blue
ID Type
1 A
1 B
2 A
3 A

Code: Green
ID Type
5 A
5 B
5 C
6 A


With the sample data above, I would want to end up with

DISTINCT TYPE COUNTS BY CODE AND ID:

Code: Blue ID: 1 DISTINCT TYPE COUNT: 2

Code: Blue ID: 2 DISTINCT TYPE COUNT: 1

Code: Blue ID: 3 DISTINCT TYPE COUNT: 1

Code: Green ID: 5 DISTINCT TYPE COUNT: 3

Code: Green ID: 6 DISTINCT TYPE COUNT: 1

Any enlightment to send my way?

 

[2drunk2funk]

Hi

I created a test table with your 3 pieces of information
Colour
ID
Type

I then keyed in the values you quoted on the example

The query below will give you the counts.

SELECT
Distincts.Colour, Distincts.ID, Count(Distincts.Type) AS CountOfType
FROM Distincts
GROUP BY Distincts.Colour, Distincts.ID;

You basically include your table of data on the Query, click the tool bar TOTAL icon (Looks like the E)
That will give you new totaling rows.

If you go into a query SQL view, paste the above and switch back to design view you will see how to achieve your goal with point and click approach

Hope that helps

 

[sohunter]

You are very kind, creating that table and all. Your example works great. Now if I can just translate that logic into my very involved crosstab query, I'll be happy! Not at the database at the moment, but will try this evening.