 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / February 2008subquery to return max date misses some records altogether?
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 Try this --- 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] and [T].[Aspect name] = ReportData.[Aspect name]);  SignatureKARL DEWEY Build a little - Test a little > I have a table with grade records for students where a particular > subject might have several grades entered on different dates. I only [quoted text clipped - 68 lines] > 0887 Claire F 11C Sports Stud C 01/06/2007 > 0887 Claire F 11C Sports Stud C 31/01/2008 > Try this --- > SELECT ReportData.Adno, ReportData.Name, ReportData.Gender, ReportData.Reg, [quoted text clipped - 3 lines] > FROM ReportData as T where [T].[Adno] = ReportData.[Adno] and [T].[Aspect > name] = ReportData.[Aspect name]); Ah, of course. Thank you that did it. Howard |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.