• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

SELECT Count(distinct field1) FROM table1 WHERE etc etc in MS Access

Is there a way of doing this in 1 query (to be used as a sub query)??? I found this example for MS SQL..

I need the count of unique values for a field...
1 Solution
Access does not support that syntax in the Count aggregate function in Access SQL.

you would need to use a sub-query:

Select Count(InnnerField) from (Select Distinct field1 as InnerField from MyTable)

mikleswAuthor Commented:
This is a Column in a query:

OptionValuecount: (SELECT Count(OV) AS countofvalues FROM (Select Distinct SQO2.standardquestionid as stdqid, SQO2.optValue as OV from standardquestionoptions as SQO2) WHERE stdQID=[standard questions].standardquestionid)


I had tried that, but access was crashing for me... I just had to move the WHERE condition outside the SELECT DISTINCT query...

seems like access doesn't support refering to a parent value from the 2nd sub-query
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now