Count Unique

[eAlchemist]

Does anyone know how to do a count unique on a field? For instance, if I had the following records:

John, blue, rectangle, square, long
John, blue, rectangle, square, short
John, blue, triangle, equilateral, n/a
John, blue, triangle, non-equi, tall

I'd like a query that results in

John, blue, 2, 3, 4

I know I can do this through layered queries, but I'm hoping to do it in one.

Thanks,
Chris

 

[BiggerBrother]

SELECT COUNT(DISTINCT column1),
COUNT(DISTINCT column2),
COUNT(DISTINCT column3),
COUNT(DISTINCT column4),
COUNT(DISTINCT column5)
FROM myTable

The above will return 1,1,2,3,4, however if you want john,blue,2,3,4 then you need to use imbedded IIF commands.

try :
SELECT IIF(COUNT(DISTINCT column1)=0,DISTINCT column1, SELECT COUNT(DISTINCT column1)),
......

It will probably need some work as I haven't been able to try it.

BB

 

[JonFer]

I believe "Count(Distinct Field)" is only available in Access 2002 and above. There was a bug with this syntax and I don't know if a fix was released or not.