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

Posted on 2006-05-09
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

    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)

    LVL 1

    Author Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now