I am trying to create a query between two tables. My two tables look like the below examples.
What I would like to do is create a query that will display all of the information in the staff list table along with the most recent date associated to that staff member. So if the staff member has two date entries I only want to display the most recent date. I do however want every date they ever receive to be listed in the dates table though as it is essentially an archive table. I have this part figured out but the part I am not sure about is the following.
I would like every staff member to show up in the list regardless if they have a date associated to them or not. And if they don't the field for them in the Augment date field should just be null. The reason for this is because I am then going to create a report based off this query that displays this information sorted first by the staff members EOD(seniority date) then next by the last date they were augmented(the most recent date in the dates table). To create this report all staff members need to be listed at all times regardless if they have had a date entry already or not. How would I go about do this? As of right now everything displays correctly but the only staff members that are displayed are people who currently have a date entered in the dates table. Thanks for everyone's time and help.