Query to Count Number of Unique Instances Across a Number of Columns

[7mgte]

I based the following SQL

SELECT COUNT(*)
FROM (
SELECT DISTINCT c1, c2, c3 FROM myTable WHERE c5=TRUE )
AS [SubTable]



here: https://social.msdn.microsoft.com/Fo...um=transactsql

For the following table

c1 c2 c3 c5
A B B TRUE
A A B TRUE
A A B TRUE
A B B FALSE
A A A TRUE

it returns 3 which is the number of unique rows. What I am looking for is a query that will not only identify unique rows, but also the number of rows with the unique pattern. So for the above table, the result would be:

A B B = 1
A A B = 2
A A A = 1

Am I asking too much of SQL in MS Access? or is there a way around this?

Any suggestions will be greatly appreciated!

Thanks!

Don

[ranman256]

its a simple count query
bring down the 3 fields as 1 field

select C1 & "-" & C2 & "-" & C3 as Fld from table
then count them
if needed make 2 queries
Q1 = select C1 & "-" & C2 & "-" & C3 as Fld from table

Q2 = select Fld , count(fld) from Q1

[7mgte]

After some tinkering, I actually managed to do it from the query design view using a totals query. Simply did a group by on each of the fields and did a count on values in a different column of the table.

Thanks!