How to find a record containing latest date?

[mshakeelattari]

I have two tables:
1.Students:

ID  Fist Name   Last Name   Date of Birth
1   ABC         XYZ         01-04-1999
2   KLM         NOP         02-06-2001
 

2.Admissions:

Admission ID    Admission No    Date of Admission    Student ID      Class of Admission
1               1230            01-04-2011           1               9th
2               3042            01-05-2011           2               10th
3               4520            05-09-2011           1               9th
 

I want the result as:

Student ID  First Name  Last Name  Date of Birth    Admission No  Date of Admission    Class of Admission
1           ABC         XYZ        01-04-1999       4520          05-09-2011           9th
2           KLM         NOP        02-06-2001       3042          01-05-2011           10th
 

Please guide me how to design a query. Thanking in advance.

[NeoPa]

Your question isn't very clear. Are you asking for a query that matches each student with the record in the Admissions table that matches their [Student ID] and also has the latest [Date of Admission]?

[mshakeelattari]

Yes. I need a query. The two tables are linked with the student ID. The latest record from Admissions table is required for each student. Thank you for quick response.

[NeoPa]

You want something like :

SELECT   [tS].[Student ID]
       , [tS].[First Name]
       , [tS].[Last Name]
       , [tS].[Date of Birth]
       , [Admissions].[Admission No]
       , [Admissions].[Date of Admission]
       , [Admissions].[Class of Admission]
FROM     [Students] AS [tS]
         INNER JOIN
         [Admissions]
  ON     [tS].[Student ID]=[Admissions].[Student ID]
WHERE    ([Admissions].[Admission ID]=(SELECT [Admissions].[Admission ID]
                                       FROM   [Admissions]
                                       WHERE  ([Admissions].[Student ID]=[tS].[Student ID])
                                         AND  ([Admissions].[Date of Admission]=(SELECT   Max([Admissions].[Date of Admission])
                                                                                 FROM     [Admissions]
                                                                                 WHERE    ([Admissions].[Student ID]=[tS].[Student ID])
                                                                                 GROUP BY [Admission ID]))))
ORDER BY [Student ID]

1. The innermost sub-query gets the maximum date for the student.
2. The next level in gets the [Admission ID] that matches both the date and the [Student ID].
3. The outer query drops any matched records between the two tables that don't match the correct [Admission ID] returned by the query from #2 above.

[mshakeelattari]

Showing Syntax Error in query expression SELECT [Admissions].[AdmissionID]
FROM [Admissions]
WHERE ([Admissions].[StudentID]=[tS].[ID])
AND ([Admissions].[DateofAdmission]=(SELECT Max([Admissions].[DateofAdmission]
FROM [Admissions].[DateofAdmission]
WHERE ([Admissions].[StudentID]=[tS].[ID])
GROUP BY [Admissions].[AdmissionID]))))


and when I removed a parenthesis from end and put one after Max([Admissions].[DateofAdmission], then the error message was replaced by: Could not find file 'C:\Users\xxx\Documents\Admissions.mdb'

[mshakeelattari]

Please note that I am using ms access 2016

[NeoPa]

mshakeelattari:
and when I removed a parenthesis from end and put one after Max([Admissions].[DateofAdmission], then the error message was replaced by: Could not find file 'C:\Users\xxx\Documents\Admissions.mdb'

You're right to make those changes. I would expect them to work, all else being equal.

From the error message I would guess that all isn't as I posted. My first guess is that the name of your table is different from what you've posted. I notice that in your first post you say there is a field called [Date of Admission], yet your error message refers to it as [DateofAdmission]. SQL isn't human. It requires everything to be exactly as they are in the database. Humans may recognise what you're referring to is 'the same', but SQL doesn't. It requires precision.

If this still persists after you've looked at the SQL again and tidied up anything that needs to be, then post your actual SQL and the exact error message and I'll see what I can do to find the error for you.

Error messages can be captured as pictures using Ctrl-C, even when it's not possible to select the actual text and copy that. Feel free to post an attached picture with the error message on if that's necessary.

PS. I'm going to change the original post now (#4) so that anyone else reading it gets the correct SQL and doesn't get confused by the errors.

[mshakeelattari]

I am really sorry for these minor differences that I posted in my first post, however, I modified the fields names according to my actual field names as are in my database. In my table "Students", the primary key field is "ID" only while in table "Admissions", the field name is "StudentID". Secondly, I have used no spaces in the names of the fields in "Admissions" table. Keeping these things in view, I am posting the SQL statement and the snapshot of the error message here.

[mshakeelattari]

I made a modification in the code provided by you and the problem vanished. However another problem appeared.

SELECT 
        tS.ID, tS.[Urdu Name],
        tS.[English Name],
        tS.[Date of Birth],
        Admissions.AdmissionNo,
        Admissions.DateofAdmission
FROM Students AS tS 
INNER JOIN Admissions 
ON tS.[ID] = Admissions.[StudentID]
WHERE (((Admissions.AdmissionID)=(SELECT [Admissions].[AdmissionID]
                                           FROM   [Admissions]
                                           WHERE  ([Admissions].[StudentID]=[tS].[ID])
                                             AND  ([Admissions].[DateofAdmission]=(SELECT   Max([Admissions].[DateofAdmission])
                                                                                     FROM     [Admissions].[DateofAdmission]
                                                                                     WHERE    ([Admissions].[StudentID]=[tS].[ID])
                                                                                     GROUP BY [Admissions].[AdmissionID])))))
ORDER BY Admissions.[StudentID];

I Deleted the underlined word:

From [Admissions].[DateOfAdmission]

in the innermost subquery.
However, a new message now appears: "At most one record can be returned by this subquery" when I run this query.

[mshakeelattari]

And at last I succeeded with the grace of my Lord Allah. I created my own query and that was successful. It is:

 
SELECT
     Students.ID,
     Students.[Urdu Name],
     Students.[English Name],
     Students.[Date of Birth],
     MyQuery2.AdmissionNo,
     MyQuery2.DateOfAdmission
  FROM
     (SELECT Admissions.AdmissionID,
             Admissions.AdmissionNo, 
             Admissions.StudentID,
             Admissions.DateOfAdmission
           FROM
              (SELECT Admissions.StudentID,
                      Max(Admissions.DateOfAdmission)
                          AS MaxOfDateOfAdmission
                  FROM Admissions
                  GROUP BY Admissions.StudentID)
                      AS MyQuery
               INNER JOIN
                          Admissions
               ON
                          MyQuery.StudentID = Admissions.StudentID
               WHERE (((Admissions.DateOfAdmission)=[MyQuery].[MaxOfDateOfAdmission]))
                          ORDER BY Admissions.StudentID)
                   AS MyQuery2
                        INNER JOIN
                                  Students
                        ON
                                  MyQuery2.StudentID = Students.ID
                        ORDER BY Students.ID
 
 

However I am grateful to you also as your help showed me the way. Thank you very much.

[NeoPa]

I'm happy for you that you've managed to find a solution. You should understand, though, that SQL of such complexity when displayed with no formatting like that, is very difficult to read and understand.

You definitely found another problem with my SQL, which I've fixed again in the original. It's very unfortunate to have been working with information that changed from post to post. As that's all I have to work with if it's not reliable then trying to get something to work - especially without having something to test it on myself - is always going to be very difficult.

You've learned something anyway. And that's no small feat. There aren't too many that can take that idea and make it work independently as you have. Well done for that. Hopefully, you've also learned the importance of starting with the right information in the question. That will save you a lot of time and effort in future.

[mshakeelattari]

I again apologize for changing information. I have formatted the SQL. Please check it for any deficiency and suggest any improvement.

[NeoPa]

mshakeelattari:
I again apologize for changing information.

I hear you. My comment wasn't intended to make you feel bad or to criticise. It was simply to hope that you've lerned from this time. I suspect you have as you seem to learn fast. Anyway, here's my latest response. Again, not being able to do a good job formatting SQL isn't a criticism. It will help you if you learn how to, but if you do you'll be one of a very small number that can.

I guess that (Reformatting the SQL) is not as easy to do as I'd imagined. The main point is to keep items which are at the same level logically, at the same indentation too. Notice in my SQL below that everything that is offset by one character is because it's contained within parentheses. Clause names like SELECT, FROM, GROUP BY, WHERE, etc. for the same query, are all in the same column.

Anyway, I moved your SQL around to make it more readable and noticed that it used a completely different approach from my suggestion. Where I used a sub-query as a filter, you used it to JOIN to the other data. Your approach should work equally as well as mine.

Your approach even has scope for losing one level of sub-query :

SELECT   [Students].[ID]
       , [Students].[Urdu Name]
       , [Students].[English Name]
       , [Students].[Date of Birth]
       , [MyQuery].[AdmissionNo]
       , [MyQuery].[DateOfAdmission]
FROM     ([Students]
          INNER JOIN
          [Admissions]
  ON      [Students].[ID]=[Admissions].[StudentID])
         INNER JOIN
         (SELECT   [Admissions].[StudentID]
                 , Max([Admissions].[DateOfAdmission]) AS [MaxOfDateOfAdmission]
          FROM     [Admissions]
          GROUP BY [Admissions].[StudentID]
          ORDER BY [Admissions].[StudentID]) AS [MyQuery]
  ON     [Students].[ID]=[MyQuery].[StudentID]
WHERE    ([Admissions].[DateOfAdmission]=[MyQuery].[MaxOfDateOfAdmission])
ORDER BY [Students].[ID]

If I'm honest, I think your approach was a better one than mine. If I were to guess, I'd say that you have a good future in database work. Good luck.

[mshakeelattari]

Lot of thanks for you!