Adding Nested IF

[Diango]

I'm trying to add a set of Nested IF statements, but it's not showing me the proper result. I have a table which shows the classes that each student has taken based on the term. So I change the letter grade they receive to a numeric value, once I get that, I need to add those numeric values. Here is the nested IIF statements.

SELECT spriden_pidm, spriden_id, spriden_last_name, spriden_first_name, sfrstcr_term_code, sfrstcr_crn, ssbsect_subj_code, ssbsect_crse_numb, sfrstcr_grde_code, GPA,

SUM(IIF( ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "A" , 4.0 ,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "A-", 3.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B+", 3.3,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B", 3.0,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "B-", 2.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C+", 2.3,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C", 2.0,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "C-", 1.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D+", 1.3,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D", 1.0,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "D-", 0.7,
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "F", 0.0,)


)))))))))))) as ppp

from temporary2

GROUP BY spriden_pidm, spriden_id, spriden_last_name, spriden_first_name, sfrstcr_term_code, sfrstcr_crn, ssbsect_subj_code, ssbsect_crse_numb, sfrstcr_grde_code, GPA

When I run this, it doesn't add the values, It just changes the value from the letter grade to the number and that's it. I need them to add. I'm I missing something?

 

[ajetrumpet]

You want to add the entire column?? If so, I would do it in a report, unless you need it in the table. Either way, I would use DSum. You probably can't do it because you are trying to combine aggregates with expressions.

Try creating a new column in the query using the temp name that you have assigned to the nested IIF statement.

E.G. - newfield = DSum("[ppp]", "table") As [Total of GPA points]

 

[Diango]

I want to add the values based on the student ID. It's not really adding the entire column, but it's adding those grade values based on each student ID.

 

[neileg]

I wouldn't do it that way. If you create a new table with the grade letters and the numeric equivalents you can simply join this table in the query and deal with the numeric field.

In any event, your final Iif() statement is missing the false value
IIF(ssbsect_subj_code in ("CHEM", "BIOL", "PHYS") and sfrstcr_grde_code = "F", 0.0,'something needs to go in here')

 

[ajetrumpet]

I want to add the values based on the student ID. It's not really adding the entire column, but it's adding those grade values based on each student ID.

You can also do it by writing another query...

SELECT [StudentID], Sum([ppp]), [whatever other fields you want]
FROM [IIF query]
GROUP BY [StudentID], [whatever other fields you want];

 

[Guus2005]

I second NEILIG's proposal.

IIF's are very useful but they also consume a lot of resources and they are extremely slow. Having said that, why don't you use a tabel whith the "A-", etc and in another field the values? Join that table to your query and it runs 50 times faster, it is easier to maintain and update!

 

[Diango]

I created the new table with the LetterGrade and Number Values. I created a Derived Table which joins the original table to the LetterGrade Values table. Then I did a left join with the original table and the derived table. That seemed to do the trick.