error. help. :(

[Tracy]

i tried to run a query in access 2003:
SELECT COUNT(DISTINCT patservices.patientkey) FROM patservices;
I received syntax error (missing operator) in query expression
'COUNT(DISTINCT patservices.patientkey) '
Then i tried
SELECT COUNT(DISTINCT (PATSERVICES.PATIENTKEY))
FROM PATSERVICES;
I received:Undefined function 'DISTINCT' in expression.

what did i do wrong? thanks alot!

 

[John Vinson]

i tried to run a query in access 2003:
SELECT COUNT(DISTINCT patservices.patientkey) FROM patservices;
I received syntax error (missing operator) in query expression
'COUNT(DISTINCT patservices.patientkey) '
Then i tried
SELECT COUNT(DISTINCT (PATSERVICES.PATIENTKEY))
FROM PATSERVICES;
I received:Undefined function 'DISTINCT' in expression.

what did i do wrong? thanks alot!

You just assumed that ACCESS SQL is ANSI compliant. It's not. In
particular, the COUNT (DISTINCT... operation is not supported in
Access.

You'll need to use a Subquery:

SELECT Count(*) FROM(SELECT DISTINCT patservices.patientkey FROM
patservices);

John W. Vinson[MVP]

 

[Tracy]

supper! Thanks alot.

You just assumed that ACCESS SQL is ANSI compliant. It's not. In
particular, the COUNT (DISTINCT... operation is not supported in
Access.

You'll need to use a Subquery:

SELECT Count(*) FROM(SELECT DISTINCT patservices.patientkey FROM
patservices);

John W. Vinson[MVP]

 

[John Vinson]

supper! Thanks alot.

Glad it helped. And I just had supper (eggplant and green pepper
marinara sauce on capellini, with Parmesean and Romano cheese)...

John W. Vinson[MVP]

 

[Guest]

sounds yummy - must try it sometime

Glad it helped. And I just had supper (eggplant and green pepper
marinara sauce on capellini, with Parmesean and Romano cheese)...

John W. Vinson[MVP]