使用标准获取具有最大日期值的行-访问2007/2010

Question

我的主表，我从中获取的所有数据是"RequestTable"(我减少了它，使它更容易)，其中我有:

ID_student
ID_professor
Date (and the three altogether are primary keys)
changeprofessor-note - if student wants to change the professor 
                       then he/she should write in that field a sentence 
                       why he/she wants to do the change
professor-reject-note - if the professor is not happy about the work of 
                        the student, then he can choose not to mentor that 
                        student anymore, leaving him without a mentor and the 
                        student should choose another mentor later.
ID-seminar- after choosing a mentor the students 
            can choose the seminar they want to work on
changeofSeminar-note - if the student wants to change the seminar 
                       then they need to write the reason why in here 
                       (then the ID of the new seminar should be written in 
                       the ID seminar field also)
IDapprove-reject - all approving or rejecting is going through this field

我最初的理论是学生可以选择导师和研讨会在一起，但现在似乎太复杂了，因为我不知道如何使一切工作后，更换导师，拒绝指导，改变研讨会等等。

我设定了一个更舒适的理论，所有的学生都需要首先选择导师。以便在需要的时候更容易获得指导的数据。我在"ID_seminar"one_answers"changeofseminar-note"下的查询中设置了"is null"，因为仅仅是seminar部分的任何更改都不会影响学生选择导师/教授并获得批准的行。

我实现了你的代码，得到了这个:

SELECT [requesttable].ID_Student, Max([requesttable].Datum) AS MaxOfDatum,                                                                        First([requesttable].ID_Profesor) AS ID_Profesor, [requesttable].ID_status_odobrenja
FROM [requesttable]
WHERE ((([requesttable].ID_Student) Not In (SELECT  [ID_Student]
FROM  [requesttable]
WHERE  [IDapprove-reject] IS NOT NULL )))
GROUP BY [requesttable].ID_Student, [requesttable].IDapprove-reject, [requesttable].changeseminar-note, [requesttable].ID_seminar
HAVING ((([requesttable].IDapprovereject)=1) AND (([requesttable].changeseminar-note) Is Null) AND (([requesttable].Id_seminar) Is Null))
ORDER BY [requesttable].ID_Student, Max([requesttable].Datum), First([requesttable].ID_Profesor), [requesttable].IDapproved-reject;

我得到:

 3   12   1
15   11   1
55    5   1

And I need:

 3   6   1
15   6   1
52   5   1 - after being rejected by mentor 10, 
             the student choose another mentor (id 5) and got approved.
55   5   1

旧信息如下:

我得到了我的查询到这一点，另外两个数据被设置为只显示具有空值的行来得到这个:

ID student Id professor date       professor-reject-note ID accept/reject
3          12           12.11.2012 null                     1 
3           6           13.11.2012 null                     1
52         10           12.11.2012 null                     1 
52         10           15.11.2012 NOT null                 1 
55          5           12.11.2012 null                     1 

我希望我的结果是

3           6           12.10.2013 null                     1
15          6           7.1.2013   null                     1
55          5           12.11.2012 null                     1

完全排除StudentID 52，因为教授拒绝通知，意思是教授不想再指导学生了。此外，我对该选项中的ID接受/拒绝号码有疑问，也许我可以将其设置为2而不是1以使其更容易。1表示接受，2表示拒绝，但如果我将其设为2并排除整个行，我仍然无法摆脱另一个ID 52行。我对它有点困惑，不知道如何使它工作。

如果我将date设置为maxdate, Id教授设置为group by FIRST，我几乎得到了我想要的，所有数据都是正确的，除了Student Id 52仍然在那里-两行。

Answer 1

您可以使用:

SELECT t.[id student],
       t.[id professor],
       t.DATE,
       t.[professor-reject-note],
       t.[id accept/reject]
FROM   atable t
WHERE  t.[id student] NOT IN 
    (SELECT [id student]
     FROM   atable
     WHERE  [professor-reject-note] IS NOT NULL) 

您的字段/列名可以做一些工作。