By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 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
Share this Question
Share on Google+
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
You need to define the lower and upper bound of each grade.

e.g. Create a lookup table as follows
tblGradeLookup
=============
ScoreLBound Decimal(18,2)
ScoreUBound Decimal(18,2)
Grade Text
Data
ScoreLBound ScoreUBound Grade
11 11.99 4b
12 12.99 4c
13 13.49 4d
13.5 13.99 4e

Create a student scores table as follows
tblStudents
==========
Student Text
Score Decimal(18,2)

Data
Student Score
a 11.2
b 12
c 13.2

You can then create a query as follows

qStudentGrades
============
SELECT tblStudents.Student, tblGradeLookup.Grade
FROM tblGradeLookup, tblStudents
WHERE (((tblGradeLookup.ScoreLBound)<=[tblstudents].[Score])
AND ((tblGradeLookup.ScoreUBound)>[tblstudents].[Score]));

This gives the following results
Student Grade
a 4b
b 4c
c 4d
--

Terry Kreft
"Penfold" <te***********@blueyonder.co.uk> wrote in message
news:iB*******************@fe1.news.blueyonder.co. uk...
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 5 '06 #5

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" <ly***********@aim.com> 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 <ly***********@aim.com> 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.

<Stolen from PSC.edu>

"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 <ly***********@aim.com> 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.
<snip>

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.