H
Howard
I have a table with grade records for students where a particular
subject might have several grades entered on different dates. I only
want the latest grade, plus other stuff like AdNo, name and gender.
Using code found here sort of works but seems to miss out some subjects
altogether! the code I used was
SELECT ReportData.Adno, ReportData.Name, ReportData.Gender,
ReportData.Reg, ReportData.[Aspect name], ReportData.Result,
ReportData.[Result Date]
FROM ReportData
WHERE (ReportData.[Result Date])=(select Max(T.[Result Date]) as maxdate
FROM ReportData as T where [T].[Adno] = ReportData.[Adno]);
but for example, using the data below, no record at all was returned for
Rel Stud for Jordan (but was for Claire) and none for Food for Claire.
All other records returned were correct.
What is going on?
Howard
(Part of the table 'Report Data' is below)
Adno Name Gender Reg Aspect name Result Result Date
0886 Jordan M 11C Engineering B 20/02/2007
0886 Jordan M 11C Engineering B 31/05/2007
0886 Jordan M 11C Engineering B 31/01/2008
0886 Jordan M 11C English D 19/02/2007
0886 Jordan M 11C English D 31/05/2007
0886 Jordan M 11C English D 30/01/2008
0886 Jordan M 11C Geography D 19/02/2007
0886 Jordan M 11C Geography D 31/05/2007
0886 Jordan M 11C Geography D 31/01/2008
0886 Jordan M 11C Maths C 01/03/2007
0886 Jordan M 11C Maths D 06/06/2007
0886 Jordan M 11C Maths C 09/10/2007
0886 Jordan M 11C Maths C 31/01/2008
0886 Jordan M 11C Music D 28/06/2007
0886 Jordan M 11C Music D 31/01/2008
0886 Jordan M 11C Rel Stud E 20/02/2008
0886 Jordan M 11C Rel Stud D 04/06/2007
0886 Jordan M 11C Science C 26/02/2007
0886 Jordan M 11C Science C 01/06/2007
0886 Jordan M 11C Science D 11/10/2007
0886 Jordan M 11C Science D 31/01/2008
0887 Claire F 11C Child Dev C 28/02/2007
0887 Claire F 11C Child Dev C 31/05/2007
0887 Claire F 11C Child Dev C 31/01/2008
0887 Claire F 11C English C 19/02/2007
0887 Claire F 11C English C 31/05/2007
0887 Claire F 11C English C 31/01/2008
0887 Claire F 11C Food B 26/02/2007
0887 Claire F 11C Food B 31/05/2007
0887 Claire F 11C Food C 10/10/2007
0887 Claire F 11C Food C 30/01/2008
0887 Claire F 11C ICT C 15/02/2007
0887 Claire F 11C ICT C 31/05/2007
0887 Claire F 11C ICT E 19/10/2007
0887 Claire F 11C ICT E 31/01/2008
0887 Claire F 11C Maths D 19/02/2007
0887 Claire F 11C Maths C 06/06/2007
0887 Claire F 11C Maths C 31/01/2008
0887 Claire F 11C Rel Stud D 01/03/2007
0887 Claire F 11C Rel Stud B 04/06/2007
0887 Claire F 11C Rel Stud B 31/01/2008
0887 Claire F 11C Science C 26/02/2007
0887 Claire F 11C Science C 01/06/2007
0887 Claire F 11C Science D 08/10/2007
0887 Claire F 11C Science D 31/01/2008
0887 Claire F 11C Sports Stud C 07/02/2007
0887 Claire F 11C Sports Stud C 01/06/2007
0887 Claire F 11C Sports Stud C 31/01/2008
subject might have several grades entered on different dates. I only
want the latest grade, plus other stuff like AdNo, name and gender.
Using code found here sort of works but seems to miss out some subjects
altogether! the code I used was
SELECT ReportData.Adno, ReportData.Name, ReportData.Gender,
ReportData.Reg, ReportData.[Aspect name], ReportData.Result,
ReportData.[Result Date]
FROM ReportData
WHERE (ReportData.[Result Date])=(select Max(T.[Result Date]) as maxdate
FROM ReportData as T where [T].[Adno] = ReportData.[Adno]);
but for example, using the data below, no record at all was returned for
Rel Stud for Jordan (but was for Claire) and none for Food for Claire.
All other records returned were correct.
What is going on?
Howard
(Part of the table 'Report Data' is below)
Adno Name Gender Reg Aspect name Result Result Date
0886 Jordan M 11C Engineering B 20/02/2007
0886 Jordan M 11C Engineering B 31/05/2007
0886 Jordan M 11C Engineering B 31/01/2008
0886 Jordan M 11C English D 19/02/2007
0886 Jordan M 11C English D 31/05/2007
0886 Jordan M 11C English D 30/01/2008
0886 Jordan M 11C Geography D 19/02/2007
0886 Jordan M 11C Geography D 31/05/2007
0886 Jordan M 11C Geography D 31/01/2008
0886 Jordan M 11C Maths C 01/03/2007
0886 Jordan M 11C Maths D 06/06/2007
0886 Jordan M 11C Maths C 09/10/2007
0886 Jordan M 11C Maths C 31/01/2008
0886 Jordan M 11C Music D 28/06/2007
0886 Jordan M 11C Music D 31/01/2008
0886 Jordan M 11C Rel Stud E 20/02/2008
0886 Jordan M 11C Rel Stud D 04/06/2007
0886 Jordan M 11C Science C 26/02/2007
0886 Jordan M 11C Science C 01/06/2007
0886 Jordan M 11C Science D 11/10/2007
0886 Jordan M 11C Science D 31/01/2008
0887 Claire F 11C Child Dev C 28/02/2007
0887 Claire F 11C Child Dev C 31/05/2007
0887 Claire F 11C Child Dev C 31/01/2008
0887 Claire F 11C English C 19/02/2007
0887 Claire F 11C English C 31/05/2007
0887 Claire F 11C English C 31/01/2008
0887 Claire F 11C Food B 26/02/2007
0887 Claire F 11C Food B 31/05/2007
0887 Claire F 11C Food C 10/10/2007
0887 Claire F 11C Food C 30/01/2008
0887 Claire F 11C ICT C 15/02/2007
0887 Claire F 11C ICT C 31/05/2007
0887 Claire F 11C ICT E 19/10/2007
0887 Claire F 11C ICT E 31/01/2008
0887 Claire F 11C Maths D 19/02/2007
0887 Claire F 11C Maths C 06/06/2007
0887 Claire F 11C Maths C 31/01/2008
0887 Claire F 11C Rel Stud D 01/03/2007
0887 Claire F 11C Rel Stud B 04/06/2007
0887 Claire F 11C Rel Stud B 31/01/2008
0887 Claire F 11C Science C 26/02/2007
0887 Claire F 11C Science C 01/06/2007
0887 Claire F 11C Science D 08/10/2007
0887 Claire F 11C Science D 31/01/2008
0887 Claire F 11C Sports Stud C 07/02/2007
0887 Claire F 11C Sports Stud C 01/06/2007
0887 Claire F 11C Sports Stud C 31/01/2008