Selecting the first or last record in a one to many relationship 1

[tony4x4]

Hi,

I have a Staff Table and a Working History table. One staff record has many Working History records. I need to 1.) create a report of the Start Date of Employment of each staff member. ie. Staff records plus 1st record in the Woking History table where StaffId is the same. 2.) I need to create a report of the current working status of all staff members ie. Staff records plus last record in the Working History table where StaffId is the same.

Please can you point me in the right direction.

Thanks

 

[rac2]

Possibly -

SELECT Staff.FirstName, wh.BeginDate, wh.Position
FROM Staff
INNER JOIN (
SELECT StaffID, MIN(DateNewStatus) AS BeginDate
FROM WorkHistory
GROUP BY StaffID
) wh ON Staff.StaffID = wh.StaffID

 

[pendragons10]

If you include MAX(date) or MIN(date) will return the Last or First Date if dates are chronological.

 

[tony4x4]

Thanks rac2 & pendragons10 for you response. Please could you help me further. I have tried to apply the above SQL statement to my access query via SQL view, but I don't seem to be winning. Please could you modify the below query to achieve the above. I am sure I am doing something wrong.

SELECT tblStaffDetails.ID, tblStaffDetails.Title, tblStaffDetails.Initials, tblStaffDetails.FirstName, tblStaffDetails.Surname, tblWorkingHistory.Date, tblWorkingHistory.EventID
FROM tblStaffDetails INNER JOIN tblWorkingHistory ON tblStaffDetails.ID = tblWorkingHistory.StaffID;

Thanks in advance

 

[rac2]

Hmmm...

tblWorkingHistory.EventID might be a key that could point to the hire record. How to get that?

I think my query fails because of wh.Position which is not actually in my GROUP BY subquery. It is like the tblWorkingHistory.EventID field.

Try the following. It is not the answer, but it should be a valid query. Meanwhile see my next post.

SELECT tblStaffDetails.ID, tblStaffDetails.Title, tblStaffDetails.Initials, tblStaffDetails.FirstName, tblStaffDetails.Surname, wh.BeginDate

FROM ( tblStaffDetails

INNER JOIN (

   SELECT StaffID,
          MIN(Date) AS BeginDate 
   FROM tblWorkingHistory
   GROUP BY StaffID ) wh

   ON tblStaffDetails.ID = wh.StaffID;

)

 

[rac2]

Make a new query from the subquery that finds the oldest row in the work history table. Call this query tblHireDate

SELECT StaffID,
          MIN(Date) AS BeginDate 
   FROM tblWorkingHistory
   GROUP BY StaffID

Next make a query that gets the hiring details from tblWorkHistory. Call it tblHiringDetails

SELECT tblWorkHistory.*, tblHireDate.BeginDate 
FROM tblWorkHistory
INNER JOIN tblHireDate ON
   tblWorkHistory.StaffID = tblHireDate.StaffID
AND tblWorkHistory.Date = tblHireDate.BeginDate

or maybe

SELECT tblWorkHistory.*, tblHireDate.BeginDate 
FROM tblWorkHistory
INNER JOIN tblHireDate ON
   tblWorkHistory.StaffID = tblHireDate.StaffID

WHERE tblWorkHistory.Date = tblHireDate.BeginDate

Then you can join tblHiringInformation with tblStaffDetails for the report.