SYNTAX sql...

[sal21]

Is thios correct to count a distinct value in field?

SELECT COUNT(DISTINCT(CAR)) FROM CAR_S

have error

[HansV]

In SQL Server, you could use

SELECT COUNT(DISTINCT CAR) FROM CAR_S

but Microsoft Access doesn't support this syntax. You can use the following instead:

SELECT COUNT(CAR) FROM (SELECT DISTINCT CAR FROM CAR_S)

or

SELECT COUNT(CAR) FROM (SELECT CAR FROM CAR_S GROUP BY CAR)

[sal21]

In SQL Server, you could use

SELECT COUNT(DISTINCT CAR) FROM CAR_S

but Microsoft Access doesn't support this syntax. You can use the following instead:

SELECT COUNT(CAR) FROM (SELECT DISTINCT CAR FROM CAR_S)

or

SELECT COUNT(CAR) FROM (SELECT CAR FROM CAR_S GROUP BY CAR)

TKS!
work.

But in order of speeding, peraph a group by and count have the best result, or not?

[HansV]

You'd have to test it on a large table. I think the difference will be small.