calculate a grade score from another table criteria (1 Viewer)

martell · Dec 28, 2007

dear all;

i've a table #student like

studentID score

Sminth 88
samantha 76
Williamv 45
martell 78

and i also have a #grade table

Grade Start end

F 0 45
E 46 55
D 56 65
C 66 75
B 76 85
A 86 100

and my question is,how to calculate the student table's grade value?
i want the result like below..
#studentID score Grade
Sminth 88 A
samantha 76 B
Williamv 45 F
martell 78 B

anyone can help me??thanks alot

regards

martell

ajetrumpet · Dec 28, 2007

martell said:
and my question is,how to calculate the student table's grade value?
i want the result like below..
#studentID score Grade
Sminth 88 A
samantha 76 B
Williamv 45 F
martell 78 B

I don't know how experienced you are with the program, but I would personally set up a function in a module to handle this. You could then call it when you run a make-table query. This might not be the best method, especially if you are going through this process a number of times. You might want to use the recordset method. But, if you want to try the function that I am referring to, write this in a new module:

Code:

Function MyGrade(MyScore as Integer) as String

  MyGrade = IIF(MyScore <= 45, "F", IIF(MyScore <= 55, "E", _

    IIF(MyScore < 65, "D", IIF(MyScore <= 75, "C",

      IIF(MyScore <= 85, "B", "A")))))

End Function

Then, in your query, create your new column by writing the function:

Code:

SELECT [other fields here], GetGrades([Score]) As [Student Grade]

You can use the IIF statement above in a number of different ways, but storing it somewhere, like in a module will allow you to use it often, without having to write it out everytime you need it.

martell · Dec 30, 2007

thanks 4 the comment,

it's works.but what if i want to add another value?i don't want to write a many code in my program,i just want to use the master criteria table.if i have a new grade criteria i dont need to write the code again.it's automatically calculate..anyone can help me?

pbaldy · Dec 30, 2007

Sure; you want a query with a non-equi join. Off the top of my head:

SELECT studentID, score , grade
FROM Students LEFT JOIN Grades ON Students.score >= Grades.Start AND Students.score <= Grades.End

Mike375 · Dec 30, 2007

Is samantha 76 in two fields ie. [samantha] and [76]

The exercise appears to be allow for an extension of

F 0 45
E 46 55
D 56 65
C 66 75
B 76 85
A 86 100

So we might have (extremes)

FFFFFFFFF -1000 -900
AAAAAAAA 1000 1100

As FFFFFF and its number braket or AAAAA and its number bracket are added to a table...........the rest happens automatically

Is that the situation?

martell · Dec 31, 2007

pbaldy said:
Sure; you want a query with a non-equi join. Off the top of my head:

SELECT studentID, score , grade
FROM Students LEFT JOIN Grades ON Students.score >= Grades.Start AND Students.score <= Grades.End

thanks..it's works bro.....

martell · Dec 31, 2007

Mike375 said:
Is samantha 76 in two fields ie. [samantha] and [76]

The exercise appears to be allow for an extension of

F 0 45
E 46 55
D 56 65
C 66 75
B 76 85
A 86 100

So we might have (extremes)

FFFFFFFFF -1000 -900
AAAAAAAA 1000 1100

As FFFFFF and its number braket or AAAAA and its number bracket are added to a table...........the rest happens automatically

Is that the situation?

the situation is i want the query automatically calculate the grade of the student score using the score table. thanks

fprogrammer · Jan 11, 2014

Can anyone tell me how to do that in SQL

pbaldy · Jan 11, 2014

calculate a grade score from another table criteria (1 Viewer)

martell

Registered User.

ajetrumpet

Banned

martell

Registered User.

pbaldy

Wino Moderator

Mike375

Guest

martell

Registered User.

martell

Registered User.

fprogrammer

New member

pbaldy

Wino Moderator

fprogrammer

New member

pbaldy

Wino Moderator

fprogrammer

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread