How do I list multiple Count statements?

[Guest]

The database I'm working in has 2 fields I'd like to Count: 1) Type, and 2)
Pattern. Each Pattern is unique, but many patterns can belong to a specific
Type. My problem is that I'd like to produce a list of DISTINCT Types with
the COUNT of how many unique Patterns belong to each DISTINCT Type. So far,
I've come up with this:

SELECT [Type], [Total Patterns]
FROM (SELECT DISTINCT [Type]
FROM [Pattern List]) AS [All Types], (SELECT COUNT([Pattern]) AS [Total
Patterns]
FROM (SELECT DISTINCT [Type], [Pattern] FROM [Pattern List] WHERE [Type] =
(SELECT DISTINCT [Type] FROM [Pattern List])) AS Patterns;


I keep getting the error "at most one record can be returned from this
subquery".
I'd like to get a list like this:

Type1 5
Type2 3
Type3 10
Type4 4

but the only way I get a result is to change the WHERE clause to [Type] =
"Type1". That gives me this:

Type1 5
Type2 5
Type3 5
Type4 5

where all the COUNT values of unique patterns are that of the first Type.


Can somebody let me know how to make the WHERE clause recurse through the
list of DISTINCT Types so that each entry in the second column of the results
table corresponds to the number of unique Patterns for that Type, not just
Type1?

 

[Tom Ellison]

Dear GW:

SELECT Type,
(SELECT COUNT(*)
FROM (SELECT DISTINCT Pattern
FROM [Total Patterns] TP1
WHERE TP1.Type = TP.Type))
AS DistinctPatternCount
FROM [Total Patterns] TP

Does this help?

I've used two levels of nested subqueries. This can be difficult for Jet,
but it has gotten better. If it fails, then you may need a separate query
to simplify this. Please let me know if this is the case.

Tom Ellison