get the last vacation record only for all employees
How could I get the last vacation record only for all employees?
Table name : TBPRSVAC
Columns: vac_emp_no, vac_vac_type, vac_start_date, vac_end_date, vac_spend_place
SELECT TBPRSVAC.* FROM TBPRSVACINNER JOIN (SELECT vac_emp_no, Max(vac_Start_Date) as MaxStartFROM TBPRSVACGroup by vac_Emp_No) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.vac_Start_Date
I know this will work to return the recordset, but it may not be updateable, depending on the database you are using.
Mohammad Alsolaiman
ASKER
PortletPaul
I had try both solutions, and they were awesome .
Dale Fye
I had encountered this error
Invalid column name 'vac_Start_Date'.
It was pointing to line Number 7
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.vac_Start_Date
Dale Fye
That should have been:
) as T ON TBPRSVAC.vac_emp_No = T.vac_Emp_No AND TBPRSVAC.vac_Start_Date = T.MaxStart
Open in new window
I know this will work to return the recordset, but it may not be updateable, depending on the database you are using.