Looking Up a Field from Another Table

Hello,

I have two tables, Table #1 and Table #2. I am essentially using Table #1 to add a person to the system... and Table #2 to add notes linked to each record in Table #1. So "Bob Jones" may have 30 different notes about him in Table #2. "Sally Fakeface" may have 7 notes about her.

Now sometimes, when Bob or Sally are acting up, a note may need to be made on them that also contains a "Status Change" flag. This would be so that we are able to track a history of the person fluctuating from the status of "On Target or "Needs Attention" through a view tied to table #2. I've been able to do all that with no problem, but what I would like to do is to allow a system user to view Table #1 with the client's name and then also their current "Status", which again, is determined by someone making notes in Table #2 about them. The goal is to give someone a quick view of all the names and where they are at, w/o having to go into the note history of each person.

So essentially, what I am asking, is how do I lookup the "Status Change" value from the last note made on Bob in Table #2 that was selected as a "Status Update" and plug that into the "Status" field of Table #1?

Here are the details:

Table #1: consists of the following fields...

Name
Status: looking for this to be a lookup of the last Status note made from Table #2 (more on this later). It would default to "On Target" when the person was added to the table.

Table #2 consists of the following fields...

Name: a drop down lookup that retrieves all names from Table #1.
Notes: multi-text field to make a note about the person.
Note Type: drop down that allows the user to select the type of note with the values of "Notes" and "Status Change".
Status Change: drop down allows the user to select between "N/A" (default), "On Target", or "Needs Attention".