I have a query called Weekly attendance, from the field total days attended, I would like an IIF function thats says if total days attended is less than 3 then it is equal to $40 ,if it is more than 3 it is equal to $75.
Any help is appreciated.
Thanks in advance.
PS: here is the SQL for the query I'm trying to add the IIF function to:
SELECT Sum((Abs([DaysPresent]))) AS [Total Days Attended], QryWeeklyAttendance.ChildID, QryWeeklyAttendance.[First Name], QryWeeklyAttendance.[Last Name]
GROUP BY QryWeeklyAttendance.ChildID, QryWeeklyAttendance.[First Name], QryWeeklyAttendance.[Last Name];
terribly sorry. I should have tested my theory before speaking. Both will work.
you can't, however, get at them in the where clause (which shouldn't be that much of a surprise when considering the processing of the statement, though it was to me initially). Evidently, I went to the wrong conclusion.
I will have to say, personally, that aliases involved in IIF functions have saved me much headache. I have a query that would be murderous without using the alias in the function.
But I have to also say that I'm not sure why aliases will work sometimes and not others. I'm not a SQL guru and don't understand how it works exactly. Can anyone explain to me why an alias wouldn't work in one part of a query but will in another? I mean, how many books out there will tell you that you can't use the alias of one field to insert it into another field as in performing a calculation on it. But when I use that same alias in a different field in an IIF function...it works. Wha-?
Thanks alot. It worked but I have another problem. How do I sum the monthly income based on the amount due weekly(am I making sense?)
Ok...what I mean is when I run the query weekly it gives me a total due for that week for each child...now if a Child paid $40 in week one because he/she attended <3 days for the week but in week 2 the same child attendance was >3 days and had to pay $75. At the end of the month can I get a summary showing $115?