How to do SELECT COUNT(DISTINCT(somecolumn))??

[Selden]

In sql server, you can do SELECT COUNT(DISTINCT(Student)) FROM StudentGrades

So if some students were in the StudentGrades table more than once, this
would give me a count of how many students there are.

But I can't figure out how to do this in Access. What's the syntax for
this?

Thanks,
---Selden McCabe

 

[TC]

IIRC, Jet SQL does not support COUNT(DISTINCT).

What about creating and saving a SELECT DISTINCT query? Then your code
could SELECT COUNT(*) on that saved query.

If you don't want to do that, maybe you could count the records for
each student where the primary key value (in that record) is the
*maximum* primary key value in any record for that student! That should
serve to count only one record per student. Something like (UNTESTED):

SELECT COUNT(*)
FROM T AS T1
WHERE EXISTS
(SELECT NULL
FROM T AS T2
WHERE T2.STUDENT = T1.STUDENT
AND T2.PK =
( SELECT MAX(T3.PK)
FROM T AS T3
WHERE T3.STUDENT = T2.STUDENT )

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[TC]

Oops, it clearly needs an extra closing bracket:

WHERE T3.STUDENT = T2.STUDENT ) )

TC