Filter A record with date lower than MaxDate

[mshakeelattari]

I have Two Tables:

Stduents:

ID     Name     DOB
==========================
1      XYZ      01/01/2000
 

Admissions:

AdmissionID   Admission No    Date of Admission     StudentID
==============================================================
1             1234            25/05/2016            1
2             4567            28/04/2015            1
3             7890            25/08/2015            1
 

Both the tables are related with
Admissions.StudentID = Students.ID

Now the question is that I want a query resulting in a record like:

StudentID    Name      AdmissionID    Date of Admission     Admissoin No
========================================================================
1            XYZ       3              25/08/2015            7890
 

Where the Date of Admission is just lesser than than maximum date of admission.
I know that that the maximum date of admission can be found using max(date of admission).
But the help is needed for the record containing the second last date of admission. Thanking in anticipation.

[PhilOfWalton]

This should work, but you will need to change my Member... to Student... and Payment... to Admission...

Be aware that if a student has only 1 admission date, the previous date will be blank.

SELECT Payments.MemberID, Payments.PaymentDate, Member.MemSurName, 
DMax("PaymentDate","Payments","MemberID = " & [Member]![MemberID]) AS MaxDate, 
DMax("PaymentDate","Payments","MemberID = " & [Member]![MemberID] & "And PaymentDate < #" & [MaxDate] & "#") AS PrevDate
FROM Payments INNER JOIN Member ON Payments.MemberID = Member.MemberID;
 

So Maxdate is the last addmission date. Then we find the maximum admission date for that student where the date is less than the MaxDate just found

Phil