Converting student scores to grades not that easy?

[Penfold]

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

[Lyle Fairfield]

Re: Converting student scores to grades not that easy?

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 ...).

[Jana]

Re: Converting student scores to grades not that easy?

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

[Lyle Fairfield]

Re: Converting student scores to grades not that easy?

maybe

Public Function GradeB(ByVal AverageScore As Currency) As String
GradeB = Int((AverageSco re - 2) / 6) + 3
GradeB = GradeB & Chr$(99 - Int((AverageSco re - (6 * GradeB) + 16) /
2))
End Function

You could use this in a query as
SELECT StudentName, GradeB(AverageS core) FROM Whatever.

[Terry Kreft]

Re: Converting student scores to grades not that easy?

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.Stu dent, tblGradeLookup. Grade
FROM tblGradeLookup, tblStudents
WHERE (((tblGradeLook up.ScoreLBound) <=[tblstudents].[Score])
AND ((tblGradeLooku p.ScoreUBound)>[tblstudents].[Score]));

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


--

Terry Kreft


"Penfold" <terrychambers@ blueyonder.co.u k> wrote in message
news:iBVuf.7655 4$PD2.13336@fe1 .news.blueyonde r.co.uk...[color=blue]
> I'd appreciate help converting student average test scores into grades. My
> problem is that I need to allocate one of about 20 grades[/color]
(3a,3b,3c,4a,4b ,4c[color=blue]
> 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[/color]
a[color=blue]
> 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
>
>[/color]

[Lyle Fairfield]

Re: Converting student scores to grades not that easy?

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.Averag eScore <= [SubmitScore];

An example of use:

Sub temp()
Dim c As Currency
Dim q As DAO.QueryDef
Set q = DBEngine(0)(0). QueryDefs("qryG rade")
For c = 0 To 40 Step 0.1
q.Parameters("S ubmitScore") = 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

[Lyle Fairfield]

Re: Converting student scores to grades not that easy?

Terry Kreft wrote:[color=blue]
> You need to define the lower and upper bound of each grade.[/color]

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.

[Terry Kreft]

Re: Converting student scores to grades not that easy?

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" <lylefairfield@ aim.com> wrote in message
news:1136476023 .081172.131580@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> Terry Kreft wrote:[color=green]
>> You need to define the lower and upper bound of each grade.[/color]
>
> 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.
>[/color]

[Lyle Fairfield]

Re: Converting student scores to grades not that easy?

Terry Kreft wrote:[color=blue]
> 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.[/color]

Agreed.

[Thelma Lubkin]

Re: Converting student scores to grades not that easy?

Lyle Fairfield <lylefairfield@ aim.com> wrote:
: maybe

: Public Function GradeB(ByVal AverageScore As Currency) As String
: GradeB = Int((AverageSco re - 2) / 6) + 3
: GradeB = GradeB & Chr$(99 - Int((AverageSco re - (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

[Lyle Fairfield]

Re: Converting student scores to grades not that easy?

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.12345 6789 than currency. The 28
part can be defined when the field is defined.
Arithmetic done on that 123456789.12345 6789 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 ...

[Thelma Lubkin]

Re: Converting student scores to grades not that easy?

Lyle Fairfield <lylefairfield@ 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