Access 2007：“ SELECT COUNT（DISTINCT ...）” [英] Access 2007: "SELECT COUNT(DISTINCT ..."

问题描述

我有一个表，其中包含StudyId，PatientId和StudyStartDateTime。我想绘制用户指定的两个日期之间的研究和患者总数。问题在于计数不同的值。查询如下：

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;

该查询几乎可以正常工作，除了它会计算具有相同StudyId或相同的PatientId。我知道Access不支持COUNT（DISTINCT ...），但是在解决此问题时遇到了很多麻烦。任何帮助将不胜感激。

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.

推荐答案

您可以尝试使用子查询进行计数，但是相关子查询往往会

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;

请注意，我将计数字段添加到每个字段的GROUP BY表达式中。

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

如果您想使其更紧凑，则可以为每个查询创建一个视图，然后将它们与StudyStartDateTime上的不同查询连接，以将结果全部集中在一个结果集中。

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.

这篇关于Access 2007：“ SELECT COUNT（DISTINCT ...）”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！