sql - Access 2007: "SELECT COUNT(DISTINCT ..."

Question

I have a table that contains a StudyId, a PatientId, and a StudyStartDateTime. I'd like to graph the totals of the Studies and Patients between two dates specified by the user. The problem is with counting distinct values. Here is the query:

SELECT
    s.StudyStartDateTime,
    COUNT(s.StudyId),
    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
ORDER BY s.StudyStartDateTime;

This query works almost as it should, except that it counts duplicates of rows with the same StudyId or the same PatientId. I know that Access doesn't support COUNT(DISTINCT...), but I'm having a lot of trouble working around this. Any help would be very appreciated.

Answer 1

Noticed the dbo_ prefix - is this linked to a SQL Server database?

If so you can use a pass-through query and use the COUNT(DISTINCT ...) syntax as it will passed directly to SQL Server.