Selecting top 2 records on same row

[SimonMurphy]

Hi, I have a little problem that I think should be able to be done but cannot
work out how I might do it in Access 2003.
I have records like the following:

Name ExamDate ExamGrade
Simon 12/10/2008 C
Simon 01/02/2009 B
Simon 20/04/2009 A
James 12/11/2007 C
James 06/05/2008 A
Laura 03/08/2006 B

For each person I want to display their current exam grade and their
previous exam grade on the same row of a report. So I am trying to develop a
query to return the records I need. If the person has only one exam date
entry I want the previous grade to show a blank.
e.g.
Name Previous Grade Current Grade
Simon B A
James C A
Laura B

Can anyone help with the SQL for the query I would need?

Thanks in advance
Simon

 

[John W. Vinson]

Hi, I have a little problem that I think should be able to be done but cannot
work out how I might do it in Access 2003.
I have records like the following:

Name ExamDate ExamGrade
Simon 12/10/2008 C
Simon 01/02/2009 B
Simon 20/04/2009 A
James 12/11/2007 C
James 06/05/2008 A
Laura 03/08/2006 B

For each person I want to display their current exam grade and their
previous exam grade on the same row of a report. So I am trying to develop a
query to return the records I need. If the person has only one exam date
entry I want the previous grade to show a blank.
e.g.
Name Previous Grade Current Grade
Simon B A
James C A
Laura B

Can anyone help with the SQL for the query I would need?

Thanks in advance
Simon

A "Self Join" query will probably work here:

SELECT A.[Name], A.[ExamGrade] AS CurrentGrade, B.[ExamGrade] AS PreviousGrade
FROM tblGrades AS A
INNER JOIN tblGrades.B
ON A.[Name]=B.[Name]
WHERE A.ExamDate = (SELECT Max(C.ExamDate) FROM tblGrades AS C
WHERE C.[Name] = A.[Name])
AND B.ExamDate = (SELECT Max(D.ExamDate) FROM tblGrades AS D
WHERE D.[Name] = A.[Name]
AND D.[ExamDate] < A.[ExamDate])

Untested air code...

 

[Douglas J. Steele]

Hi, I have a little problem that I think should be able to be done but
cannot
work out how I might do it in Access 2003.
I have records like the following:

Name ExamDate ExamGrade
Simon 12/10/2008 C
Simon 01/02/2009 B
Simon 20/04/2009 A
James 12/11/2007 C
James 06/05/2008 A
Laura 03/08/2006 B

For each person I want to display their current exam grade and their
previous exam grade on the same row of a report. So I am trying to develop
a
query to return the records I need. If the person has only one exam date
entry I want the previous grade to show a blank.
e.g.
Name Previous Grade Current Grade
Simon B A
James C A
Laura B

Can anyone help with the SQL for the query I would need?

Thanks in advance
Simon

A "Self Join" query will probably work here:

SELECT A.[Name], A.[ExamGrade] AS CurrentGrade, B.[ExamGrade] AS
PreviousGrade
FROM tblGrades AS A
INNER JOIN tblGrades.B
ON A.[Name]=B.[Name]
WHERE A.ExamDate = (SELECT Max(C.ExamDate) FROM tblGrades AS C
WHERE C.[Name] = A.[Name])
AND B.ExamDate = (SELECT Max(D.ExamDate) FROM tblGrades AS D
WHERE D.[Name] = A.[Name]
AND D.[ExamDate] < A.[ExamDate])

Untested air code...

That won't work for the case where the person only has a single exam date
though, will it John? (Sorry, too tired to try and work out the SQ)

 

[John W. Vinson]

That won't work for the case where the person only has a single exam date
though, will it John? (Sorry, too tired to try and work out the SQ)

oops... missed that. True, you'll need a Left Join and probably a subsidiary
query or two.

 

[SimonMurphy]

Many thanks John and Douglas for your help,
I will give it a go and see how I get on.

Simon