ms access count distinct column

Question

The following code gives syntax error in count distinct values.

SELECT SUM(house_thanas.target)               AS target, 
       SUM(reports_db.ach_total)              AS Total, 
       Count(DISTINCT reports_db.report_date) AS WorkingDays 
FROM   ((areas 
         INNER JOIN distribution_houses 
                 ON areas.area_id = distribution_houses.area_id) 
        INNER JOIN house_thanas 
                ON distribution_houses.distribution_house_id = 
                   house_thanas.distribution_house_id) 
       INNER JOIN reports_db 
               ON house_thanas.thana_id = reports_db.thanaid 
WHERE  reports_db.report_date BETWEEN 
       #2012/02/10 12:00:00 am# AND #2013/02/09 11:59:59 pm#

how to find distinct count values in microsoft access database?

possible duplicate of [query to count number of different values?](http://stackoverflow.com/questions/13851607/query-to-count-number-of-different-values) — HansUp, Feb 09 '13 at 06:54
Access SQL does not support `Count(Distinct ...)`. See this blog post for an Access alternative: http://blogs.office.com/b/microsoft-access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx — HansUp, Feb 09 '13 at 06:55
Every where use group by clause but group by can make my result different — decoder, Feb 09 '13 at 07:20

Fionnuala · Accepted Answer · 2013-02-09T13:00:22.017

0

I think you are not seeing what is being suggested, which is:

 SELECT SUM(house_thanas.target)               AS target, 
   SUM(reports_db.ach_total)              AS Total, 
   (SELECT Count(*) As JustOnce
    FROM (SELECT DISTINCT report_date FROM reports_db)) AS WorkingDays 

 <...>

In the query design window, design view, the subquery should be set as Expression.

edited Feb 09 '13 at 13:00

answered Feb 09 '13 at 12:20

Fionnuala

90,370
7
114
152

ms access count distinct column

1 Answers1