CODEWITHSUNDEEP
sqlms-access
Preston Richardson
Preston Richardson

Reputation: 21

Access query: iif function error

I am trying to count in a column Answers > 8 and in another column < 8 from the same field Answers.[Answer (#)]. The field Answers.[Answer (#)] is survey results on a 1-10 scale. I get the error "cannot have aggregate function in expression (IIf(Answers.[Answer (#)] >8, sum(count(Answers.[Answer (#)])),""))".

In the iif function if I take out sum( the query will count individually 9s and 10s and the same goes for numbers 8 and under. I need those aggregated together in 1 row. 

SELECT Questions.Year, Questions.[Question Text],
IIf(Answers.[Answer (#)] >8, sum(count(Answers.[Answer (#)])),"") AS Promotors, 
IIf(Answers.[Answer (#)]<=8, sum(Count(Answers.[Answer (#)])),"") AS Detractors
FROM Answers INNER JOIN Questions 
ON Answers.QuestionID = Questions.[Access ID]
WHERE (((Questions.[Question Text]) Like '*Recommend*')) 
and questions.[Question Text] not like '*Are there other technology         services*'
GROUP BY Questions.Year, Questions.[Question Text], Answers.[Answer (#)];

Upvotes: 0

Views: 230

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you want conditional aggregation. Something like this:

SELECT Questions.Year, Questions.[Question Text],
       SUM(IIf(Answers.[Answer (#)] > 8, 1, 0) AS Promotors, 
       SUM(IIf(Answers.[Answer (#)] <= 8, 1, 0) AS Detractors
FROM Answers INNER JOIN
     Questions 
     ON Answers.QuestionID = Questions.[Access ID]
WHERE Questions.[Question Text]) Like '*Recommend*' AND
      questions.[Question Text] not like '*Are there other technology         services*'
GROUP BY Questions.Year, Questions.[Question Text];

You can't nest aggregation functions. You want the condition as the argument to the function, not on the result. I also removed the answer column from the group by.

Upvotes: 1

Related Questions