"IF" statement in query

[ChewDoggie]

G'day ALL!

Is it possible to do an "ElseIf" in an Access Query?

Currently, the query looks like this:

sql = "SELECT ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, "
sql = sql & "Sum(IIf(re.Status = 'Y' And re.eventid = " & entId & ", 1)) As [YES] "
sql = sql & "FROM racer ra LEFT JOIN registration re ON ra.racerid = re.racerid "
sql = sql & "GROUP BY ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city "
sql = sql & "order by ra.lname, ra.fname"

Thanks!




AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[rjoubert]

You would need to nest your IIF statements...

IIF([Condition 1], 1, IIF([Condition2], 2, IIF([Condition 3], 3, 0)))

 

[ChewDoggie]

One step closer...

I'm not that savvy on the Access query thing. Here's what I'm trying to achieve:

I have a datagrid that displays a racer's last name, first name, and the number of classes the racer is registered in. The "IIf" statement in the above query displays the "number of registered classes". I'd like to modify the query. If the status = 'Y', then display the number of registered classes. If the status = 'P' then I want to display something like "PRE-" plus the number of registered classes.

Anyone know how I might accomplish this?

Thanks!


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[rjoubert]

Try this, let me know how it turns out...

IIF(re.Status = 'Y', (Sum(IIf(re.Status = 'Y' And re.eventid = " & entId & ", 1))), IIF(re.Status = 'P', "PRE-" & (Sum(IIf(re.Status = 'Y' And re.eventid = " & entId & ", 1)))))

Good Luck!

 

[ChewDoggie]

Thanks rjoubert !

I executed your code but got an error. THen I modified the code slightly to look like this:

SELECT ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, IIF(re.Status = 'Y' and re.eventid = 98, (Sum(IIf(re.Status = 'Y' And re.eventid = 98, 1))), IIF(re.Status = 'P' and re.eventid = 98, 'PRE-' & (Sum(IIf(re.Status = 'P' And re.eventid = 98, 1))))) As [YES] FROM racer ra LEFT JOIN registration re ON ra.racerid = re.racerid GROUP BY ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city order by ra.lname, ra.fname

but received the following error (the same error that your code sent):

You tried to execute a query that does not include the specified expression 'IIf(re.Status='Y' And re.eventid=98,Sum(IIf(re.Status='Y' And re.eventid=98,1)),IIf(re.Status='P' And re.eventid=98,'PRE-' & Sum(IIf(re.Status='P' And re.eventid=98,1))))' as part of an aggregate function.

I keep trying different variations of the above query. Thanks for your T & E, I appreciate it.




AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[rjoubert]

I would suggest splitting this into two queries...one where you use the IIF expression to display a value of 1 if your conditions are met, and the second query would use the first query, and do the sum of that value.

 

[lespaul]

the aggregate error comes from not having every field in the select statement that is NOT an aggregate listed in the GROUP BY clause:

SELECT ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, IIF(re.Status = 'Y' and re.eventid = 98, (Sum(IIf(re.Status = 'Y' And re.eventid = 98, 1))), IIF(re.Status = 'P' and re.eventid = 98, 'PRE-' & (Sum(IIf(re.Status = 'P' And re.eventid = 98, 1))))) As [YES] FROM racer ra LEFT JOIN registration re ON ra.racerid = re.racerid GROUP BY ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city IIF(re.Status = 'Y' and re.eventid = 98, (Sum(IIf(re.Status = 'Y' And re.eventid = 98, 1))), IIF(re.Status = 'P' and re.eventid = 98, 'PRE-' & (Sum(IIf(re.Status = 'P' And re.eventid = 98, 1)))))
order by ra.lname, ra.fname

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ChewDoggie]

THanks to all.

Modified said query to:

sql = "SELECT ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, "
sql = sql & "IIF(re.Status = 'Y' and re.eventid = " & entId & ", (Sum(IIf(re.Status = 'Y' And re.eventid = " & entId & ", 1))), IIF(re.Status = 'P' and re.eventid = " & entId & ", 'PRE-' & (Sum(IIf(re.Status = 'P' And re.eventid = " & entId & ", 1))))) As [YES] "
sql = sql & "FROM racer ra LEFT JOIN registration re ON ra.racerid = re.racerid "
sql = sql & "GROUP BY ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, IIF(re.Status = 'Y' and re.eventid = " & entId & ", (Sum(IIf(re.Status = 'Y' And re.eventid = " & entId & ", 1))), IIF(re.Status = 'P' and re.eventid = " & entId & ", 'PRE-' & (Sum(IIf(re.Status = 'P' And re.eventid = " & entId & ", 1))))) "
sql = sql & "order by ra.lname, ra.fname"

...and received an error:

Cannot have aggregate function in GROUP BY clause (IIf(re.Status='Y' And re.eventid=98,Sum(IIf(re.Status='Y' And re.eventid=98,1)),IIf(re.Status='P' And re.eventid=98,'PRE-' & Sum(IIf(re.Status='P' And re.eventid=98,1)))))

Thanks for the input !!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ChewDoggie]

Then I modified my "Group by" clause to this:

sql = "SELECT ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, "
sql = sql & "IIF(re.Status = 'Y' and re.eventid = " & entId & ", (Sum(IIf(re.Status = 'Y' And re.eventid = " & entId & ", 1))), IIF(re.Status = 'P' and re.eventid = " & entId & ", 'PRE-' & (Sum(IIf(re.Status = 'P' And re.eventid = " & entId & ", 1))))) As [YES] "
sql = sql & "FROM racer ra LEFT JOIN registration re ON ra.racerid = re.racerid "
sql = sql & "GROUP BY ra.racerid, ra.lname, ra.fname, ra.amanum, ra.tempnumber, ra.city, [YES] "
sql = sql & "order by ra.lname, ra.fname"

and still received the error:

You tried to execute a query that does not include the specified expression 'IIf(re.Status='Y' And re.eventid=98,Sum(IIf(re.Status='Y' And re.eventid=98,1)),IIf(re.Status='P' And re.eventid=98,'PRE-' & Sum(IIf(re.Status='P' And re.eventid=98,1))))' as part of an aggregate function.

Anyway....much appreciation to lespaul !!



AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[lespaul]

what exactly are you trying to do with the IIF statement and the SUMs?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[ChewDoggie]

With the modified code, I'm trying to illustrate that a racer is "PRE-registered" or "registered", and the number of classes they're (pre)registered in. If the racer is PRE-registered, the status field = "N". If registered, the status field = "Y".

HTH

Thanks!


AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[lespaul]

So what if you have a separate query that gets the RacerID and the count of each:

SELECT RacerID, StatusField, Count(*) FROM TableName GROUP BY RacerID, StatusField

then join into that query and based on the status you can determine if it's a registration or a pre-registration.

Is the eventID the 'class' they are registered for? If so, can a person be registered multiple times for the same 'eventId'?

Leslie

 

[ChewDoggie]

The "eventid" is a unique identifier for that particular race Event. But for every "status" entry appearing in the table, there is a corresponding "classid" for that status. So, I'm just counting the status fields (= "Y") to get the number of classes they're registered for.



AMACycle

American Motorcyclist Association

http://www.ama-cycle.org