MS Access SQL - 计数记录数= TRUE

Question

我有什么看起来相当简单，但我在这里失去了一些东西。我试图显示所有不同的[业务流程]值，并计算[需要纠正措施] = TRUE的每个[业务流程]的记录数。可能有一些[业务流程]潜在的零记录有[解决方法是要求]值= TRUE，或者有可能是值> 0。这是我使用的SQL：

SELECT [Business Process], sum([Corrective Action Is Required]) as [Total Corrective Action Is Required] 

FROM [Review Results] 

WHERE [Corrective Action is Required] in 

(select IIF([Corrective Action Is Required] = TRUE, 1, 0) from [Review Results]) 

GROUP BY [Business Process] 

ORDER BY [Business Process]; 

目前SQL在[需要整个纠正措施]的所有记录中显示0值。有人能指出我做得不对吗？

Answer 1

这样做会不会更容易？还记得检查您的基础数据。

SELECT [Business Process], count([Corrective Action Is Required]) as [Total Corrective Action Is Required] 
FROM [Review Results] 
WHERE [Corrective Action Is Required] = 1 
GROUP BY [Business Process] 
ORDER BY [Business Process]; 

Answer 2

在Access中，真= -1和False = 0，所以怎么样：

SELECT [Business Process], 
     Sum(Abs([Corrective Action Is Required])) 
      as [Total Corrective Action Is Required] 
FROM [Review Results] 
GROUP BY [Business Process] 

你不需要排序，当你有分组依据。

Answer 3

可以总结一个布尔值，但在访问您将得到阴性结果为真= -1 - 所以这应该工作

SELECT [Business Process], sum(iif([Corrective Action Is Required] = TRUE, 1, 0)) as [Total Corrective Action Is Required] 
FROM [Review Results] 
GROUP BY [Business Process] 
ORDER BY [Business Process]