When I used Count () function to count distinct rows in MS Access, I got the error for the syntax of this Count expression.

Here is the query:

SELECT ColA, COUNT(DISTINCT ColB)
FROM TAB1
GROUP BY ColA

I issued the above query in DB2 and there is no error. Anyone has any idea why Access complains expression syntax of the above query?

Thanks!

You can't use DISTINCT keyword here.

if you really need it, alex, there is a way to do this with a microsoft access "view" i.e. query of query

however, you need to confirm that you really want select colA, count(distinct colB) rather than select colA, count(colB) because they are different

what you do is define a query like select distinct colA, colB and then save this, after which you can do select colA, count(colB) from savedquery

if this isn't clear, tell me your columns and i'll rework the queries


rudy