deciding minimum passing marks & distinction grade

[cliff]

I have a student databases containing marks scored for grade 1X . Now I want
to innovate and improve the students performance and decided to fix mimumum
pass marks & distinction for individual subject based on previous years
marks scored students :-

Last years performance

No. students marks
4 29
8 30
21 52
8 65
6 70
3 80
---
50
---
tblmarks:-
subjectcode s_name stu_code marks
abc maths 122 42
xyz socialscience 122 45

I want to set mimimum passing is least marks scored by not less than 10%
of total students and similarly distinction is fixed on maximim marks scored
by less then 10% of the students per subject. In the above case minimum
passing marks are 30 and to get distinction grade is 80, which I want store
above information automatically in table. can u help me to store above result
in table and table will look like this

tblgrade
tblsubject passing distinction
abc 30 80
xyz 35 85
lmn 25 70
pqr 40 85
------ -----
total 130 320
----- ----

finally passing & distinction creteria is based on conditions :-
1) secured minimum in individual subject and also total marks obtained
is not less than 130 & 320 .

thanks
cliff

 

[Dale Fye]

Cliff,

Think you are going to need three queries

1. The first will compute the Passing mark for each subject, and should
look something like:

SELECT Temp.Subject,
Min(Temp.Mark) AS Passing
FROM tbl_Student_Scores AS Temp
WHERE ((Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject
AND Mark <= Temp.Mark)/
(Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject))>=0.1
GROUP BY Temp.Subject;

2. The second will be used to compute the score required for distinction,
and should look something like:

SELECT Temp.Subject,
Min(Temp.Mark) AS Distinction
FROM tbl_Student_Scores AS Temp
WHERE ((Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject
AND Mark >= Temp.Mark)/
(Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject))<=0.1
GROUP BY Temp.Subject;

You might have to play with this second one to decide whether you want the
minimum Mark, where the percentage score is higher than the 90th percentile,
or the maximum number where the percentage score <= 90th percentile.

3. The last query would join these two to get the combination of subject,
minPassing and Distinction marks.

 

[Dale Fye]

Cliff,

Think you are going to need three queries

1. The first will compute the Passing mark for each subject, and should
look something like:

SELECT Temp.Subject,
Min(Temp.Mark) AS Passing
FROM tbl_Student_Scores AS Temp
WHERE ((Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject
AND Mark <= Temp.Mark)/
(Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject))>=0.1
GROUP BY Temp.Subject;

2. The second will be used to compute the score required for distinction,
and should look something like:

SELECT Temp.Subject,
Min(Temp.Mark) AS Distinction
FROM tbl_Student_Scores AS Temp
WHERE ((Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject
AND Mark >= Temp.Mark)/
(Select Count(*) FROM tbl_Student_Scores
WHERE Subject = Temp.Subject))<=0.1
GROUP BY Temp.Subject;

You might have to play with this second one to decide whether you want the
minimum Mark, where the percentage score is higher than the 90th percentile,
or the maximum number where the percentage score <= 90th percentile.

3. The last query would join these two to get the combination of subject,
minPassing and Distinction marks.