Access 2007: "Select Count(Distinct ..."

Access 2007: SELECT COUNT(DISTINCT ...

You could try doing it with subqueries for the counts, but correlated sub-queries tend to bite when it comes to performance.

If you are open to doing this in two queries instead of one, these would work:

SELECT
    s.StudyStartDateTime,
    COUNT(s.PatientId)
FROM
    dbo_Study_ViewX211_Rpt AS s
WHERE
    s.StudyStartDateTime>=Forms![StudiesPatientsByDate]!txtStartDate,
    s.StudyStartDateTime<=Forms![StudiesPatientsByDate]!txtEndDate
GROUP BY s.StudyStartDateTime, s.PatientId
ORDER BY s.StudyStartDateTime;

SELECT
    s.StudyStartDateTime,
    COUNT(s.StudyId),
FROM
    dbo_Study_ViewX211_Rpt AS s
WHERE
    s.StudyStartDateTime>=Forms![StudiesPatientsByDate]!txtStartDate,
    s.StudyStartDateTime<=Forms![StudiesPatientsByDate]!txtEndDate
GROUP BY s.StudyStartDateTime, s.StudyId
ORDER BY s.StudyStartDateTime;

Note that I added the counted fields to the GROUP BY expressions in each.

If you want to make it more "compact" you could create a view for each of these queries and join them to a distinct query on StudyStartDateTime to get the results all in one resultset.

Count Distinct in a Group By aggregate function in Access 2007 SQL

I'm not expert in MS Access and it is quite a long time last time I have written anything for it, but this maybe will work:

SELECT cd.DiagCode, Count(cd.CustomerID)
FROM (select distinct DiagCode, CustomerID from CustomerTable) as cd 
Group By cd.DiagCode;

How do I count unique items in field in Access query?

Try this

SELECT Count(*) AS N
FROM
(SELECT DISTINCT Name FROM table1) AS T;

Read this for more info.

COUNT DISTINCT MS ACCESS

Generally handled by using a inline view in MSAccess. The inline view gets you the distinct order_status by client and then you can count.

SELECT clientID
FROM (SELECT ClientID, order_status 
      FROM orders 
      WHERE order_status IN("pending", "shipped")  
      GROUP BY clientID, order_status) B
GROUP BY ClientID
HAVING count(*) > 1

Or (because I don't like group by w/o aggregation)

SELECT clientID
FROM (SELECT Distinct ClientID, order_status 
      FROM orders 
      WHERE order_status IN("pending", "shipped")) B
GROUP BY ClientID
HAVING count(*) > 1

SELECT DISTINCT count() in Microsoft Access

MS Access does not support count(distinct). In your case, you can use a subquery. In addition, your query should not work. Perhaps this is what you intend:

SELECT COUNT(*)
FROM (SELECT ApplixCalls.OurRef
      FROM ApplixCalls LEFT JOIN
           Correspondence
           ON ApplixCalls.OurRef = Correspondence.OurRef
      WHERE (((orrespondence.OurRef Is Null) AND (ApplixCalls.Position) <> 'Closed')) OR
              (ApplixCalls.Position <> 'Closed') AND (Correspondence.[SBSUpdate?] = True))
            )
      GROUP BY ApplixCalls.OurRef
    ) as x;

Modifications:

• You have a HAVING clause with no GROUP BY. I think this should be a WHERE (although I am not 100% sure of the logic you intend).
• The SELECT DISTINCT is replaced by SELECT . . . GROUP BY.
• The COUNT(DISTINCT) is now COUNT(*) with a subquery.

EDIT:

Based on the description in your comments:

SELECT COUNT(*)
FROM (SELECT ApplixCalls.OurRef
      FROM ApplixCalls LEFT JOIN
           Correspondence
           ON ApplixCalls.OurRef = Correspondence.OurRef
      WHERE (((orrespondence.OurRef Is Null) AND (ApplixCalls.Position) <> 'Closed')) OR
              (ApplixCalls.Position <> 'Closed') AND (Correspondence.[SBSUpdate?] = True))
            )
      GROUP BY ApplixCalls.OurRef
      HAVING SUM(IIF(Correspondence.[SBSUpdate?] = False, 1, 0)) = 0
    ) as x;

SELECT Count Distinct Syntax MS Access SQL

MS Access does not support COUNT(DISTINCT). You can use two aggregations:

select count(*)
from (select distinct [Customer Number]
      from Test 
     ) as t;

Note: This counts NULL values whereas COUNT(DISTINCT) does not. You can filter them out in either the subquery or outer query if that is an issue.

MS ACCESS: How can i count distinct value using access query?

try

select ..., count(distinct Training.Tcode) as ..., ...

EDIT - please now look at this...

Take the following SQL code. The first select is how SQL server would do this and the second query should be access compliant...

declare @t table (eCode int, tcode int)
insert into @t values(1,1)
insert into @t values(1,1)
insert into @t values(1,2)
insert into @t values(1,3)
insert into @t values(2,2)
insert into @t values(2,3)
insert into @t values(3,1)    

select 
    ecode, count(distinct tCode) countof
from
    @t
group by
    ecode

select ecode, count(*)
from
    (select distinct tcode, ecode
    from  @t group by tcode, ecode) t
group by ecode

It returns the following:

ecode tcode
1       3 (there are 3 distinct tcode for ecode of 1)
2       2 (there are 2 distinct tcode for ecode of 2)
3       1 (there is 1 distinct tcode for ecode of 3)

how do I do a count distinct group by in ms access?

You can use two levels of aggregation:

SELECT [dataset].[ID], COUNT(*) AS distinct_dates
FROM (SELECT DISTINCT id, dates
      FROM [dataset]
     ) as d
GROUP BY [dataset].[ID];

Strictly speaking this is not 100% the same, because this would count NULL as a separate value (but count(distinct) ignores NULL values). So, to be identical, you would need to use:

SELECT [dataset].[ID], COUNT(dates) AS distinct_dates

---

Related Topics