How to grade student's mark?

[Azalina]

I want to grade "A", "B", "C" etc on my students score.
The table is:
ID | Student | Mark | Grade
1 Lea 23
2 Adam 70
3 Ron 50

The scale of grade is:
Mark Grade
0-39 F
40-50 D
51-60 C
61-70 B
80-100 A

And the grade of each of the student is then stored in "Grade" column...I need help. Thank you so much.

[malcolmk]

Try using select...case code on exit event of Mark field and store result in Grade.

[NeoPa]

Unfortunately your posted markings are wrong. There is a gap of 9 or 10 marks between A and B.

Essentially though, you need some code similar to the following to show the evaluated grade :

SELECT ...
     , [Mark]
     , IIf([Mark] < 40,
           'F',
           IIf([Mark] < 50,
               'D',
               IIf([Mark] < 60,
                   'C',
                   IIf([Mark] < 70,
                       'B',
                       'A')))) AS [Grade]
FROM   [YourTable]

Unless you want problems, don't save the evaluated grade. Evaluate it as and when required, but don't save it (See Database Normalisation and Table structures for why).

[ADezii]

This would be the classic example of where a Banding Table should be used. It is much easier to show you an example, via the Attachment, rather then to provide an explanation. I added an arbitrary B+ Grade to fill the gap between 71 and 79.

P.S. - Hope you didn't mind, but I included myself in the Demo, and also gave myself an A. (LOL).

[NeoPa]

I agree. A banding table would be a better solution for such as this. This table would contain data as in the following :
Table = [tblGrade]

[MaxMark]  [Grade]
    40        F
    50        D
    60        C
    70        B
   100        A

The SQL to use this then would be :

SELECT   tS.ID
       , tS.Student
       , tS.Mark
       , Max(tG.Grade) AS [Grade]
FROM     [tblStudent] AS tS
         INNER JOIN
         [tblGrade] AS tG
  ON     tS.Mark <= tG.MaxMark
GROUP BY tS.ID
       , tS.Student
       , tS.Mark
ORDER BY tS.Student

[ADezii]

I thought taht you were going to disagree on the Grade I gave myself! (LOL).

[NeoPa]

No. You get top marks from me. You saw the better solution when I posted the noddy one.