Access – Add Left Join to Return Value Based on Primary Key (PK)

I need help querying a user name based on its primary key value in table.

Another user @gordon-linoff helped me create a query to pull the most recent NoteDate for every OpportunityID in a table. I wanted to expand this to provide the NotesUser, which is part of the same table as the other two elements.

My challenge is NotesUser returns the UserID (which is a PK) and not the UserName.

Here is an example. This code:

SELECT o.OpportunityID, n.NotesDate, n.NotesID, n.NotesUser
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;

Produced this result:

NotesUser is the PK of a table called tblUsers, which contains two columns. ID and UserName

Here the code I’ve got so far

SELECT o.OpportunityID, n.NotesDate, n.NotesID, n.NotesUser, tblUsers.UserName
    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) LEFT JOIN 
                                                                (SELECT tblUsers.UserName
                                                                 FROM tblUsers 
                                                                 WHERE n.NoteUser=tblusers.username) 
    ORDER BY o.OpportunityID;

This code throws a syntax error in the FROM statement.

Update: Here is what the tblUsers looks like.

Answer

If I understand correctly, you can just add a LEFT JOIN. No subquery is necessary:

SELECT o.OpportunityID, n.NotesDate, n.NotesID, n.NotesUser,
       u.username
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
     ) LEFT JOIN
     tblUsers as u
     ON n.NoteUser = u.id
ORDER BY o.OpportunityID;