CODEWITHSUNDEEP
sqlms-access
Dylan Kramer
Dylan Kramer

Reputation: 21

SQL Statement won't work

SELECT Attendance.EventID, Events.[Event Name], Sum(Attendance.Attend) AS SumOfAttend, IIf([SumOfAttend]>30,"Over","Under") AS OverUnder30

When i run this SQL statement in Access 2013 Access pops up a parameter box and wants me to the value for SumOfAttend. I want to test the SumOfAttend value that is already calculated and if it is over 30 then display over in another field and if it is under 30 I want it to display under

Thank you

Upvotes: 0

Views: 171

Answers (1)

JNevill
JNevill

Reputation: 50019

Assuming you removed the FROM clause here on purpose... You can use the following SELECT clause to do what you want:

SELECT Attendance.EventID, Events.[Event Name], Sum(Attendance.Attend) AS SumOfAttend, IIf(Sum(Attendance.Attend)>30,"Over","Under") AS OverUnder30

This way you aren't relying on Access to figure out what you mean by [SumOfAttend]

Upvotes: 1

Related Questions