449,318 Members | 1,613 Online
Need help? Post your question and get tips & solutions from a community of 449,318 IT Pros & Developers. It's quick & easy.

# Converting student scores to grades not that easy?

 P: n/a I'd appreciate help converting student average test scores into grades. My problem is that I need to allocate one of about 20 grades (3a,3b,3c,4a,4b,4c etc through to 8c plus a couple of others). This comes up as too complex using nested IIf, then, else. I tried a lookup table but here the problem I found was that unless the average test score matches exactly then no grade is returned. For example a score of 11.0 returns grade 4b but a score of 11.1 will not. I tried formatting the number field (for score) to fixed number with no decimal places but this had no effect. I guess I would like the lookup table to to handle >10.9 = "4b" or similar but the table format errors saying I entered text into a number field. Any suggestions would be most welcome. Terry Jan 4 '06 #1
11 Replies

 P: n/a I think we could help if you define the range clearly. What range of average test scores maps to a grade of 4b? If you were to give us two consecutive ranges it would, I think, be quite clear (maybe ...). Jan 4 '06 #2

 P: n/a Penfold: You could go a couple of ways on this. First way would be to use a custom function and a Select Case statement. Second way, using a Lookup table would be to define a lower and upper boundary for each grade, and then use your lookup to find the grade that falls between the boundaries. Something like this: tblGrade Grade (text) LowerScore (Number, Double) UpperScore (Number, Double) 4b 10.5 11.5 HTH, Jana Jan 4 '06 #3

 P: n/a maybe Public Function GradeB(ByVal AverageScore As Currency) As String GradeB = Int((AverageScore - 2) / 6) + 3 GradeB = GradeB & Chr\$(99 - Int((AverageScore - (6 * GradeB) + 16) / 2)) End Function You could use this in a query as SELECT StudentName, GradeB(AverageScore) FROM Whatever. Jan 5 '06 #4

 P: n/a The table: AverageScore Grade \$36.00 8a \$34.00 8b \$32.00 8c \$30.00 7a \$28.00 7b \$26.00 7c \$24.00 6a \$22.00 6b \$20.00 6c \$18.00 5a \$16.00 5b \$14.00 5c \$12.00 4a \$10.00 4b \$8.00 4c \$6.00 3a \$4.00 3b \$2.00 3c \$0.00 W The query (a saved query named 'qryGrade'): SELECT TOP 1 SubQuery.Grade FROM [SELECT Grade, AverageScore FROM Grades ORDER BY AverageScore DESC]. AS SubQuery WHERE SubQuery.AverageScore <= [SubmitScore]; An example of use: Sub temp() Dim c As Currency Dim q As DAO.QueryDef Set q = DBEngine(0)(0).QueryDefs("qryGrade") For c = 0 To 40 Step 0.1 q.Parameters("SubmitScore") = c Debug.Print c, q.OpenRecordset().Collect(0) Next c End Sub The results (reduced to .5 increments to fit in Immediate Window) 0 W 0.5 W 1 W 1.5 W 2 3c 2.5 3c 3 3c 3.5 3c 4 3b 4.5 3b 5 3b 5.5 3b 6 3a 6.5 3a 7 3a 7.5 3a 8 4c 8.5 4c 9 4c 9.5 4c 10 4b 10.5 4b 11 4b 11.5 4b 12 4a 12.5 4a 13 4a 13.5 4a 14 5c 14.5 5c 15 5c 15.5 5c 16 5b 16.5 5b 17 5b 17.5 5b 18 5a 18.5 5a 19 5a 19.5 5a 20 6c 20.5 6c 21 6c 21.5 6c 22 6b 22.5 6b 23 6b 23.5 6b 24 6a 24.5 6a 25 6a 25.5 6a 26 7c 26.5 7c 27 7c 27.5 7c 28 7b 28.5 7b 29 7b 29.5 7b 30 7a 30.5 7a 31 7a 31.5 7a 32 8c 32.5 8c 33 8c 33.5 8c 34 8b 34.5 8b 35 8b 35.5 8b 36 8a 36.5 8a 37 8a 37.5 8a 38 8a 38.5 8a 39 8a 39.5 8a 40 8a Jan 5 '06 #6

 P: n/a Terry Kreft wrote: You need to define the lower and upper bound of each grade. I cannot agree with you, Terry. Defining a <= minimum score for each grade is entirely sufficient. If the upper bound is not equal to the lower bound of the of the next cut then there must be an infinite number of scores with no grade. If the upper bound is equal to the lower bound of the of the next cut then defining one if these is redundant. Jan 5 '06 #7

 P: n/a If the difference between the upper bound of each band and the lower bound of the next band is equivalent to the accuracy of measurement then all grades will fall in a band. Having said that, yours works whether the above is true or not. -- Terry Kreft "Lyle Fairfield" wrote in message news:11**********************@g14g2000cwa.googlegr oups.com... Terry Kreft wrote: You need to define the lower and upper bound of each grade. I cannot agree with you, Terry. Defining a <= minimum score for each grade is entirely sufficient. If the upper bound is not equal to the lower bound of the of the next cut then there must be an infinite number of scores with no grade. If the upper bound is equal to the lower bound of the of the next cut then defining one if these is redundant. Jan 6 '06 #8

 P: n/a Terry Kreft wrote: If the difference between the upper bound of each band and the lower bound of the next band is equivalent to the accuracy of measurement then all grades will fall in a band. Agreed. Jan 6 '06 #9

 P: n/a Lyle Fairfield wrote: : maybe : Public Function GradeB(ByVal AverageScore As Currency) As String : GradeB = Int((AverageScore - 2) / 6) + 3 : GradeB = GradeB & Chr\$(99 - Int((AverageScore - (6 * GradeB) + 16) / : 2)) : End Function Is there a special advantage in using Currency for the score instead of the Decimal of Terry Kreft or the Number of Jana Bauer? It seems to introduce a quirk that needs extra documentation to reassure the reader? --thelma Jan 6 '06 #10

 P: n/a The help file says: "The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important." I use Currency for simple arithmetic (fixed-point calculations in which accuracy is particularly important). **** The number Jana uses is a Double. "The IEEE double precision floating point standard representation requires a 64 bit word, which may be represented as numbered from 0 to 63, left to right. The first bit is the sign bit, S, the next eleven bits are the exponent bits, 'E', and the final 52 bits are the fraction 'F':" I like Double. The Base Ten numerals Access displays for a Double are approximations. IMO, those who expect calculations to agree with their early education calculations should not use Double, coz they won't. (This is the basis for the numerous posts we see here about Access Aritnmetical Errors)" **** The Decimal Terry uses is JET 4.0 decimal which works somewhat like currency but allows for much larger numbers (10 ^ 28) and many more "places" of decimals, eg, 123456789.123456789 than currency. The 28 part can be defined when the field is defined. Arithmetic done on that 123456789.123456789 as a Double may or may not give us the results we expect. Arithmetic done on that number as a Decimal is likely to please everyone. I like the Decimal as well. When I use it I must remember that it exists only as a variant in VBA, and thus, is a trifle clumsy and that the order of bits in that variant are not so straightforward as in the double described above. IN Ms-SQL Server it can be much bigger (10 ^ 38); I don't know if this will ever cause any problems to one switching back and forth from MS-SQL to JET or not. (variant decimal via the poster kaniest) typedef struct tagDEC { USHORT wReserved; BYTE scale; BYTE sign; ULONG Hi32; ULONGLONG Lo64; } DECIMAL; The conversions (to whole numbers) that Currency and Decimal do before doing any calculation may slow things down; I have no evidence or experience with that. Fast fractions type calulcations can be achieved by using Double and sluffing the work off to a DLL compiled in VB or whatever (which will use the floating point processor efficiently). But .... back to ... I will like the results but will everyone? I think you are right. The name Currency is confusing to many. Probably I haven't been precise enough and probably I have rambled on and probably I haven't answered you question. Oh, well ... Jan 7 '06 #11

 P: n/a Lyle Fairfield wrote: : The help file says: : "The Currency data type is useful for calculations involving money and : for fixed-point calculations in which accuracy is particularly : important." : I use Currency for simple arithmetic (fixed-point calculations in which : accuracy is particularly important). : **** : The number Jana uses is a Double. Thanks. Saved to my reference-files. Now if only I had a project to work on... --thelma Jan 7 '06 #12

### This discussion thread is closed

Replies have been disabled for this discussion.