title: Count + IIF - Access query
body: <p>Employees of the company are divided into categories A, B and C regardless of the division they work in (Finance, HR, Sales...)</p>
  
  <p>How can I write a query (Access 2010) in order to retrieve the number of employees for each category and each division?</p>
  
  <p>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.</p>
  
  <p>I thought an <code>IIF()</code> nested in a <code>COUNT()</code> would do the job but it actually counts the total number of employees instead of giving the breakdown by category.</p>
  
  <p>Any idea?</p>
  
  <pre><code>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:];
  </code></pre>
  
  <p>My aim is to code a single sql statement and avoid writing sub-queries in order to calculate the values for each division.</p>