Access - LEFT JOIN Query Most Recent Record Only

Question

I am trying to construct a LEFT JOIN query where the "right" side of the query is only the most recent record related to the "left" side's PK.

The left side of the query is from a table called Oppportunity. This table's PK is OpportunityID.

The right side of the query is from a table called tblNotes containing NotesID as its PK and NotesDate as one of its data fields. tblNotes also contains OpportunityID as a FK.

Here is my SQL:

SELECT Opportunity.OpportunityID, tblNotes.NotesDate, tblNotes.NotesID
FROM Opportunity LEFT JOIN tblNotes ON Opportunity.OpportunityID = tblNotes.opportunityid 
ORDER BY Opportunity.OpportunityID;

And here is the result:

Here is what I need including the empty fields.

Based on help I got in another post, I know I could use the MAX NoteID for each OpportunityID. Here is what I tried.

SELECT Opportunity.OpportunityID, tblNotes.NotesDate, tblNotes.NotesID
FROM Opportunity LEFT JOIN tblNotes ON Opportunity.OpportunityID = tblNotes.opportunityid INNER JOIN
                              (SELECT n.NotesID
                                FROM tblNotes AS n
                                WHERE n.notesId = (select top 1 n2.notesId FROM tblNotes as n2
                                  )         
ORDER BY Opportunity.OpportunityID;

Any help the community can offer is most appreciated. I am very good with Excel but Access is very new to me.

Answer 1

You can get the most recent row first before joining:

SELECT o.OpportunityID, n.NotesDate, n.NotesID
FROM Opportunity as o LEFT JOIN
     (SELECT n.*
      FROM tblNotes as n
      WHERE n.NotesDate = (SELECT MAX(n2.NotesDate)
                           FROM tblNotes as n2
                           WHERE n2.OpportunityID = n.OpportunityID
                          )
     ) as n
     ON o.OpportunityID = n.opportunityid 
ORDER BY o.OpportunityID;