Help with SQL Select

[Lxmm]

Hi,
Can any one help me with the following query please?
I have 2 tables in my access table to store student admission and result details as follows

Master table
StudentID Name DateOfAdmission
==========================
000001 A. Jones 11/11/05
000002 J. Martin 12/11/05
000003 B. Norton 11/11/05
000004 X.Smith 12/11/05

ExamResults table
ExamID StudentID ExamDate TotalMarks
==========================
000X 000001 10/12/05 25
000Y 000001 11/12/05 75
000Z 000001 12/12/05 10

000Y 000002 17/12/05 75
000Z 000002 14/12/05 10

000Y 000004 15/12/05 65
000Z 000004 17/12/05 40
000Y 000004 14/12/05 35
000A 000004 17/12/05 10
000B 000004 15/12/05 85
000C 000004 12/12/05 45

I want to select all details from the Student Master table and only the first exam details from ExamResults table for a given Admission date.

i.e If I choose 11/11/05 as the admission date, then I need the following results

StudentID Name DateOfAdmission ExamID ExamDate TotalMarks
000002 J. Martin 12/11/05 000Z 14/12/05 10
000004 X.Smith 12/11/05 000C 12/12/05 45

Any help on this will greatly appreciated.
Thanks in advance

 

[ca8msm]

You are best off asking specific SQL questions in one of the SQL forums but in this case it's quite simple. Simply use the Min function on the DateOfAdmission fiels and group by the rest of the fields.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.