Count(Distinct) - doesn't work on access

[ronan76]

Hi.

SELECT COUNT(DISTINCT column(s)) FROM table doesn't work on an Access database...

Is there an alternative funtion i can use to get the same result as - COUNT(DISTINCT column(s))

Thanks in advance for the help.
Ronan

 

[DJN]

You can use Count(*) or Count([ColumnName]) Have a look at the help file for further info.

 

[ronan76]

HI,
I had a look at the help, it's pretty crap to be honest but i found something about DISTINTROW which i am messing around with now.

I know how to count, i just wanted to count unique records in a row..
Count(DISTINCT record) doesn't work.

Does anyone know the syntax for doing this?
Thanks

 

[neileg]

Works as a nested query. Do a Select Distinct. Then base a count query on that. If that works, you must be able to do it in one pass as SQL, but I'm a duffer at SQL.

 

[ronan76]

Thanks man, i'll give that a go

 

[ronan76]

This Worked for me...

"SELECT Count(*) AS intTotal14 FROM (SELECT DISTINCT policy_number FROM Lives WHERE Lives.affilliation='staff scheme' and leaving_date is NULL);"

Thanks