如何计算访问权限中的学生排名 [英] How to calculate student rank in access

问题描述

我想根据下表根据他们的观察得出的学生排名.假设任何学生在班级中得分最高，但他/她在任何一门学科上均不及格，则不应考虑其排名.

I want to calculate student rank based on their obtmarks as per below tables. Suppose any student scored highest marks in their class but he/she fail in any one subjects then they shouldn't consider for rank.

1.表名称为"resultdata"

满分为(1000)

及格分数是33

ID  |   subject ID   | subject        | fullmarks | obtmarks |passmarks
1   | 1              |  HINDI         |   100     |  80      | 33
2   | 2              |  ENGLISH       |   100     |  90      | 33
3   | 3              |  MATHEMATICS   |   100     |  76      | 33
4   | 4              |  SOCIAL SCIENCE|   100     |  69      | 33

也喜欢其他主题.

2.表格名称为结果"

 ID|result    | student |student|mother |father |class|term/    |rollno|section|
   |date      | ID      |name   |name   |name   |     |semester |      |       |
1  |11.09.2019|   1     |Jasmine|Eliana |Ritesh |  8  |1st Term |  10  |   A   | 
2  |11.09.2019|   2     |Kiyas  |Fanny  |Rajnish|  10 |1st Term |  1   |   B   |
3  |11.09.2019|   3     |Ena    |Rashmi |Prakash|  9  |1st Term |  12  |   C   |
4  |11.09.2019|   4     |Sunaina|Ankita |Chander|  7  |1st Term |  15  |   A   |
5  |11.09.2019|   5     |Ankit  |Sujata |Roy    |  8  |1st Term |  11  |   B   |
6  |11.09.2019|   6     |Krishna|Bala   |Gopal  |  8  |1st Term |  5   |   C   |
7  |11.09.2019|   7     |Ranga  |Hima   |Hitesh |  9  |1st Term |  7   |   A   |
8  |11.09.2019|   8     |Suraj  |Priya  |Hemal  |  7  |1st Term |  10  |   B   |
9  |11.09.2019|   9     |Saurabh|Archana|Suyog  |  10 |1st Term |  9   |   B   |

3.表格名称为主题"

 ID  | subject        | fullmarks |  passmarks
 1   |  HINDI         |  100      |  33
 2   |  ENGLISH       |  100      |  33
 3   | MATHEMATICS    |  100      |  33
 4   | SOCIAL SCIENCE |  100      |  33
 5   | Computer       |   50      |  20

也喜欢其他主题.

主题表的ID与resultdata表的subjectID有关系.

ID of subjects table and subjectID of resultdata table has relationship.

如何使用公式或vba代码解决此问题?

How to resolve this issue using a formula or vba code?

• 条件1:根据获得的总分数计算每个学生的等级.但是任何学生在任何科目上都没有通过考试，他们将不考虑进入前10名.
• 条件2:根据获得的总成绩来计算每个学生的排名.

我在查询中尝试了此公式，但是它不起作用:

I tried this formula in query but it does not work:

Rank: DCount("*","resultdata","[fullmarks]>" & [obtmarks])+1

推荐答案

如果您不想包括成绩不及格的学生，请进行初步查询以消除他们.然后使用该查询对其余学生进行排名.考虑:

If you don't want to include students with any failing grade then do a preliminary query to eliminate them. Then use that query to rank the remaining students. Consider:

查询1:通过

SELECT resultdata.StudentID, Sum(resultdata.obtmarks) AS SumOfobtmarks
FROM resultdata
GROUP BY resultdata.StudentID
HAVING resultdata.StudentID Not In (SELECT resultdata.StudentID
       FROM resultdata
       WHERE resultdata.obtmarks<[passmarks]);

Query2:

SELECT Passing.SumOfobtmarks, Passing.StudentID, 
    (SELECT Count(*) FROM Passing AS T1 WHERE T1.SumOfobtmarks > Passing.SumOfobtmarks)+1 AS Rank
FROM Passing
ORDER BY Passing.SumOfobtmarks DESC;

但是，如果多个学生的分数相同，则结果可能不会令人满意.这是带有许多示例的常见主题.对于一项评论， http://allenbrowne.com/ranking.html .最佳解决方案可能是涉及临时"表的解决方案，如艾伦的文章所述.或尝试使用Lebans序列化功能，链接在Allen的文章中.另一个展示这些技术的优秀教程 http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063 .

However, if multiple students have same score, results will likely not be satisfactory. This is a common topic with many examples. For one review http://allenbrowne.com/ranking.html. Best solution might be one involving a 'temp' table as explained in Allen's article. Or try Lebans Serialize function, link is in Allen's article. Another excellent tutorial demonstrating these techniques http://www.mendipdatasystems.co.uk/rank-order-queries/4594424063.

这篇关于如何计算访问权限中的学生排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！