LetterGrade

[meenctg]

I have a Field in my query. The name of field is Percentage. I wanna create a new field base on this Percentage field. It will be LetterGrade with the condition below….

80 or above A+
75-below 80 A
70-below 75 A-
65-below 70 B+
60-below 65 B
55-below 55 B-
50-below 55 C+
45-below 50 C
40-below 45 D
Below 40 F

My created query is given below
LetterGrade: IIf([Percentage]>=80,"A+",IIf([Percentage]<80,"A",IIf([Percentage]<75,"A-",IIf([Percentage]<70,"B+",IIf([Percentage]<65,"B",IIf([Percentage]<60,"B-",IIf([Percentage]<55,"C+",IIf([Percentage]<50,"C",IIf([Percentage]<45,"D","F")))))))))

I have create a query but But i don't get my wanted result. It shows always A+ for any number.
Please any body help me to solve this?

 

[stopher]

A percentage is normally stored as a number between 0 and 1 e.g. 0.5 is 50%. So where you have >=80 you probably need to change to >=0.8 etc

hth
Chris

 

[recyan]

Check out if below gives some guidelines "

 TheNewSwitchGrade : 
 Switch(
	[ThePercentage]>=80,"A+" ,
	[ThePercentage]>=75 AND [ThePercentage]<80,"A" ,
	[ThePercentage]>=70 AND [ThePercentage]<75,"A-" ,
	[ThePercentage]>=65 AND [ThePercentage]<70,"B+" ,
	[ThePercentage]>=60 AND [ThePercentage]<65,"B" ,		
	[ThePercentage]>=55 AND [ThePercentage]<60,"B-" ,
	[ThePercentage]>=50 AND [ThePercentage]<85,"C+" ,
	[ThePercentage]>=45 AND [ThePercentage]<50,"C" ,
	[ThePercentage]>=40 AND [ThePercentage]<45,"D" ,
	[ThePercentage]<40 ,"F" 
 )

 TheNewIfGrade :
 iif
 (
	[ThePercentage]>=80,
	"A+",
	iif
	(
		[ThePercentage]>=75 AND [ThePercentage]<80,	
		"A" ,
		iif
		(
			[ThePercentage]>=70 AND [ThePercentage]<75,
			"A-" ,
			iif
			(
				[ThePercentage]>=65 AND [ThePercentage]<70,
				"B+" ,
				iif
				(
					[ThePercentage]>=55 AND [ThePercentage]<60,
					"B-" ,
					iif
					(
						[ThePercentage]>=50 AND [ThePercentage]<85,
						"C+" ,
						iif
						(
							[ThePercentage]>=45 AND [ThePercentage]<50,
							"C" ,
							iif
							(
								[ThePercentage]>=40 AND [ThePercentage]<45,
								"D" ,
								"F"
							)
						)
					)
				)
			)
		)
	)
 )

Edit : Sorry Stopher, was typing while you were posting.

Thanks

 

[meenctg]

A percentage is normally stored as a number between 0 and 1 e.g. 0.5 is 50%. So where you have >=80 you probably need to change to >=0.8 etc

hth
Chris

it's just name Percentage (no sign of percentage). it has number like 1 to 100

 

[Bryan]

EDIT: I think I duplicated Recyan :banghead:

I think this is what you want. Adjust your expression according to your table, fields and grade ranges.

SELECT tblGrades.Grade, 
IIf([grade]>=90,"A",
IIf([grade]>=80 AND [grade]<90,"B",
IIF([grade]>=70 AND [grade]<80,"C",
IIF([grade]>=60 AND [grade]<70,"D",
IIF([grade]<60,"F"))))) 
AS LetterGrade
FROM tblGrades;

 

[recyan]

Was wondering, what if we used the reverse logic ?

TheNewReverseSwitchGrade_1: 
Switch(
[ThePercentage]<40,"F",
[ThePercentage]<45,"D",
[ThePercentage]<50,"C",
[ThePercentage]<55,"C+",
[ThePercentage]<60,"B-",
[ThePercentage]<65,"B",
[ThePercentage]<70,"B+",
[ThePercentage]<75,"A-",
[ThePercentage]<80,"A",
[ThePercentage]>=80,"A+"
)

Thanks

 

[John Big Booty]

The scheme you have proposed will not work, as how will the Switch() know which result to apply for a value of say 61, as 61 is < 65, it is also < 70, and < 75 and < 80. You will still need to set upper and lower bounds for all options other than your top and bottom, as described in post #3 by recyan

 

[recyan]

Hi,

http://www.techonthenet.com/access/functions/advanced/switch.php

"The Switch function will return the value associated with the first expression that evaluates to TRUE."

Based on this, should not the logic work ?

Thanks

 

[John Big Booty]

Sorry my bad you are correct. Works a treat, just confirmed in my sand box

 

[recyan]

Hi,

Thanks for the confirmation on SWITCH().
Not sure, but I think the IIF() will also work along the same lines.

Thanks

 

[John Big Booty]

In deed it will

 

[recyan]

Hi,

Thanks for the confirmation.

Thanks