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

Posted on 2006-05-09
Medium Priority
Last Modified: 2012-06-21
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...
Question by:miklesw
LVL 44

Accepted Solution

Arthur_Wood earned 500 total points
ID: 16638443
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)


Author Comment

ID: 16638629
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question