subquery to return max date misses some records altogether?

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
 
K

KARL DEWEY

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]);

--
KARL DEWEY
Build a little - Test a little


Howard said:
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
Click to expand...
 
H

Howard

KARL said:
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]);
Click to expand...
Ah, of course. Thank you that did it.
Howard
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding records to filtered query via linked table 3
Trying to recreate bootable CD from zipped files 14
Possible getpivotdata with offset? 1
Please help with formula (multiple ifs to determine due date) 3
Delete Duplicate Rows, by Date field 4
Multiple search criteria to return one response 3
Selecting value from second list when date is within date range on first list 5
Export CSV dash is \226 (unicode) 3

Top