计数+ IIF - 访问查询 [英] Count + IIF - Access query

问题描述

公司的员工分为A，B和C类，不论他们在哪个部门工作（财务，人力资源，销售...）

Employees of the company are divided into categories A, B and C regardless of the division they work in (Finance, HR, Sales...)

如何我写一个查询（Access 2010），以检索每个类别和每个部门的员工人数？
最终输出将是一个excel表，其中公司部门将在A列中，A列在B列中，B列在D列中，C列在C列中。

How can I write a query (Access 2010) in order to retrieve the number of employees for each category and each division? The final output will be an excel sheet where the company divisions will be in column A, Category A in column B, category B in column and category C in column D.

我认为嵌套在Count（）中的IIF（）可以完成这项工作，但它实际上是计算员工总数，而不是按类别分类。

I thought an IIF() nested in a Count() would do the job but it actually counts the total number of employees instead of giving the breakdown by category.

任何想法？

SELECT tblAssssDB.[Division:], Count(IIf([Category]="A",1,0)) AS Count_A, Count(IIf([Category]="B",1,0)) AS Count_B, Count(IIf([ET Outcome]="C",1,0)) AS Count_C
FROM tblAssssDB
GROUP BY tblAssssDB.[Division:];

我的目的是编写单个sql语句，避免编写子查询以计算值

My aim is to code a single sql statement and avoid writing sub-queries in order to calculate the values for each division.

推荐答案

..因此，无论 [Category] ​​的值如何，每一行计数为1。

Count counts every non-Null value ... so you're counting 1 for each row regardless of the [Category] value.

保持 Count ...

Count(IIf([Category]="A",1,Null))

否则切换到 Sum ...

Sum(IIf([Category]="A",1,0))

这篇关于计数+ IIF - 访问查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！