I am trying to run a query that includes an if statement with an aggregate function. The goal is to count the number of women in the table if the pwbox =2. If the pwbox is not 2, I want the query to return "0", not null.
When I execute the SQL statement below, I get the error message "you tried to execute a query that does not include the specific expression..."
How can I rewrite the query? I see that the problem is related to categorizing my IIf statement as an expression rather than in the group by clause, but I'm not sure how to fix it. I am not an advanced SQL user, so please keep that in mind.
SELECT TBADataForm.year, TBADataForm.term, IIf([pwbox]<>"2","0",Count([womanname])) AS [maternal death tba]
GROUP BY TBADataForm.year, TBADataForm.term, IIf([pwbox]<>"2","0",Count([womanname]));