Access 2010：找到选择成绩的学生 [英] Access 2010: Find students with select grades

问题描述

我刚刚创建了Access 2010数据库，用于分析考试中的学生成绩。我能够得到所有学生的成绩。有六个科目。找到谁在所有六个都很容易，但我现在想做的是找到谁在任何五分之六的A级。同样，在任何两个科目中谁错过A。谁在一个主题失败？两个科目？我只是找不到一个办法做到。非常感谢任何帮助。

I have just made an Access 2010 database for analyzing students' grades in an exam. I was able to get grades for all students right. There are six subjects. Finding who got A in all six was easy, but what I want to do now is find who got A grade in any five out of six. Similarly, who missed A in any two subjects. Who failed in one subject? Two subjects? I just couldn't find a way to do it. Any help is greatly appreciated.

PS：我想到的是一个带有两个文本或组合框的表单，其中您将成绩放在一个和主题在另一个得到结果。例如，您在第一个选择B，在第二个选择三个以获得在任何三个科目中获得B成绩的学生的列表。只有我不知道该如何做。

PS: What I have on my mind is a form with two text or combo boxes where you put Grades in one and Subjects in the other to get the result. For example, you select B in the first and Three in the second to get the list of students who got B grade in any three subjects. Only I don't know how it can be done.

我做了什么来找到所有科目获得A的学生：使用A作为查询中的条件。 / p>

What I did to find students who got A for all subjects: Used A as the criteria in the query.

SELECT 
    exam_grade.SNAME, 
    exam_grade.[Language Score], 
    exam_grade.[English Grade], 
    exam_grade.[Language Grade], 
    exam_grade.[Phy Grade], 
    exam_grade.[Chem Grade], 
    exam_grade.[Bio Grade], 
    exam_grade.[Math Grade], 
    exam_grade.DIVISION, 
    exam_grade.Result
FROM exam_grade
WHERE (((exam_grade.[English Grade])="A") 
    AND ((exam_grade.[Language Grade])="A") 
    AND ((exam_grade.[Phy Grade])="A") 
    AND ((exam_grade.[Chem Grade])="A") 
    AND ((exam_grade.[Bio Grade])="A") 
    AND ((exam_grade.[Math Grade])="A"));

推荐答案

根据你的例子，你想做什么并且只是遵循你的逻辑，因为我认为你有一个算法解决，我可以建议：

According to your example and what do you want to do and just following your logic because I think that you have an algorithm for resolve, I can suggest this:

首先，你需要为每个成绩，例如，我简化了三个等级。

First, you need to put a value for each grade, for example I simplify the grades in three.

与他们的价值一致的A，B，C

• A = 3


• B = 2


• C = 1

然后，您需要对更改的值求和，如下所示：

Then you need to sum the changed values, like this:

 SELECT * FROM
    (SELECT id_student,
        CONVERT(INT,CASE language_grade
            WHEN 'A' THEN '3' 
            WHEN 'B' THEN '2' 
            WHEN 'C' THEN '1'
        END)
        +
        CONVERT(INT,CASE english_grade
            WHEN 'A' THEN '3' 
            WHEN 'B' THEN '2' 
            WHEN 'C' THEN '1'
        END)
        +
        CONVERT(INT,CASE chem_grade
            WHEN 'A' THEN '3' 
            WHEN 'B' THEN '2' 
            WHEN 'C' THEN '1'
        END) AS RESULT
 FROM test) TB
 WHERE RESULT >= 6

好吧，按照你的表格示例，你可以在第一个组合框中选择'A'成绩，然后你输入数字2，这意味着你想至少2个主题在成绩上至少为A，如果你的成绩A的值为3，则可以将该值乘以第二个文本框中的数字，结果为6，因此可以推断else rigth？ ;）。 （不要忘记使用等于，大于或等于，等等，以更改您的查询。）

Well, following your form example, you can select 'A' grade in first combobox, Then you put number 2, this mean that you want at least 2 subjects with at least 'A' in grade, if you that you grade 'A' has value of three, you can multiply that value to the number put in the second textbox, and that result is 6, so you can deduce else rigth? ;). (Dont forget to use equal,greater than or equal, etc, to change your query.)

PSDT

我知道这不是访问sintax，但如果你可以翻译访问sintax，这将工作。

I know that this isn't access sintax, but if you can "translate" to access sintax, this will work.

这篇关于Access 2010：找到选择成绩的学生的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！