Convert SQL with CASE to Access

Question

How can I complete the following in access

SELECT MONTH([Date]) as monthNum, 
AVG(CASE WHEN loc ='1' THEN  [Count] END) AS locAVG, 
AVG(CASE WHEN dept ='50' THEN  [Count] END) AS deptAVG, 
FROM [table]
GROUP BY MONTH([Date]) 

Answer 1

Assuming [Count] is the name of a field and you want locAVG to be the average of [Count] for the rows where loc ='1', you can take advantage of the fact that the AVG() function ignores Null values. So use an IIf() expression to feed [Count] to AVG() when loc ='1' but Null otherwise.

Use a similar IIf() expression for deptAVG.

SELECT MONTH([Date]) as monthNum, 
    AVG(IIf(loc ='1', [Count], Null)) AS locAVG, 
    AVG(IIf(dept ='50', [Count], Null)) AS deptAVG, 
FROM [table]
GROUP BY MONTH([Date]); 

Answer 2

You can use IIF in this case;

AVG( IIF(loc = "1", 1, 0) ) AS locAVG,