CODEWITHSUNDEEP
sqlms-access
UserX
UserX

Reputation: 107

Calculating Letter Grades From Numeric Values In Microsoft Access

I have a column in MS Access that contains course grade information for students. I am trying to calculate a letter grade in another column based on the contents of the grade column.

Below is what I have so far:

IIf([Grade]>92.5,"A",

IIf([Grade]>89.5,IIf([Grade]<92.5,"A-",

IIf([Grade]>86.5,IIf([Grade]<89.5,"B+",

IIf([Grade]>82.5,IIf([Grade]<86.5,"B",

IIf([Grade]>79.5,IIf([Grade]<82.5,"B",

IIf([Grade]>69.5,IIf([Grade]<79.5,"C",

IIf([Grade]<69.5,"F"))))))))))))

This works as intended for "A" and "A-" but all the other course grades come out as blanks.

Here is the "A" and "A-" working alone:

IIf([Grade]>92.5,"A",IIf([Grade]>89.5,IIf([Grade]<92.5,"A-")))

Any help will be greatly appreciated.

Upvotes: 1

Views: 2089

Answers (2)

Parfait
Parfait

Reputation: 107567

Your immediate issue is that in your nested IIF, you placed the low end first and then the high end second. Simply reverse and you output all matching letters but miss out on 0.5 endpoints.

IIf([Grade]>92.5,"A",

IIf([Grade]<92.5, IIf([Grade]>89.5,"A-",

IIf([Grade]<89.5, IIf([Grade]>86.5,"B+",

IIf([Grade]<86.5, IIf([Grade]>82.5,"B",

IIf([Grade]<82.5, IIf([Grade]>79.5,"B-",

IIf([Grade]<79.5, IIf([Grade]>69.5,"C",

IIf([Grade]<69.5,"F")))))))))))) As Letter

Alternatively, with less IIF use BETWEEN or > AND <. And since BETWEEN includes both end points and to be exhaustive, consider adding .49999999.

IIf([Grade]>92.5,"A",    
    IIf([Grade] BETWEEN 89.5 AND 92.49999999,"A-",    
        IIf([Grade] BETWEEN 86.5 AND 89.49999999,"B+",    
            IIf([Grade] BETWEEN 82.5 AND 86.49999999,"B",    
                IIf([Grade] BETWEEN 79.5 AND 82.49999999,"B-",    
                    IIf([Grade] BETWEEN 69.5 AND 79.49999999,"C",    
                        IIf([Grade] <= 69.49999999,"F", NULL))))))) As Letter2,

But even then only the high points with > are needed since you nest all conditions together.

IIf([Grade]>92.5,"A",
    IIf([Grade] > 89.5,"A-",
       IIf([Grade] > 86.5,"B+",
          IIf([Grade] > 82.5,"B",
             IIf([Grade] > 79.5,"B-",
                IIf([Grade] > 69.5,"C",
                    IIf([Grade]<=69.5,"F", NULL))))))) As Letter3

However, as @MatBailie suggests, consider a lookup table and run a range join query (with comma separated tables without any JOIN) then filter by values. Consider even incorporating above 100 (for extra credit) and below zero with room to actually hit 0.5 endpoints. This also provides better maintenance in case you adjust grade points.

GradeLetter table 

ID  Letter  MinValue      MaxValue
1   A           92.5          1000
2   A-          89.5    92.4999999
3   B+          86.5    89.4999999
4   B           82.5    86.4999999
5   B-          79.5    82.4999999
6   C           69.5    79.4999999
7   F          -1000    69.4999999

Range Join Query

SELECT l.Letter, g.Grade
FROM Grades g, GradeLetter l
WHERE g.Grade 
BETWEEN l.MinValue AND l.MaxValue;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

could be that using Switch is a bit more simple eg

SELECT
    Switch(
        [Grade]>92.5,"A",
        [Grade]>=89.5, "A-",
        [Grade]>=86.5, "B+",
        [Grade]>=82.5, "B",
        [Grade]>=79.5, "B",
        [Grade]>=69.5, "C",
        [Grade]<69.5, "F") AS my_grade
FROM my_table

Upvotes: 4

Related Questions