Show most recent record from one table with data from another

[Guest]

Hi,

I have two tables - 'staff' and 'appraisals'. The 'staff' table holds basic
employee information, the 'appraisal' table holds dates of appraisal meetings
and comments for that appraisal.

I’d like to create a query that shows me all the staff information for all
members of staff (who have at least one entry in the appraisal table)
together with the information for their latest appraisal (latest by the date
held in the ‘appraisal’ table).

I found it relatively simple to create a query that shows the information
for every entry in the ‘appraisal’ table, but limiting it to just the latest
has beaten me!

A simplified table structure:

STAFF
st_id, st_name, st_location, st_post

APPRAISAL
ap_id, ap_st_id, ap_date, ap_comment

As you can no doubt tell, the table relationship is based on st_id/ap_st_id.

Thanks in advance for any help that you can offer.

 

[Guest]

The first step is to get the most recent record from the appraisal table, for
each employee. The way I usually do this is by creating a nested subquery.

SELECT A1.*
FROM Appraisals A1
INNER JOIN
(SELECT Appraisals.EmployeeID, MAX(Appraisals.App_Date) as Max_Date
FROM Appraisals
GROUP BY Appraisals.EmployeeID) as A2
ON A1.EmployeeID = A2.EmployeeID
AND A1.App_Date = A2.Max_Date

You can then join this query to your Employees table on the EmployeeID to
get the appropriate Employee information.

HTH
Dale

 

[Guest]

Dale,

Thank you very much. I can't pretend that I absolutely understand it - but
it works!

Regards,

GATChW

 

[Guest]

Glad I could help.

The SELECT statement inside the ( ) is a nested subquery. You could just as
easily (actually it would probably be easier) created that select statement
as a separate query. The purpose of it is to get the max(app_date) for each
employee. The advantage of making it a nested subquery is that you don't end
up with a separate query.

Dale