Query records with latest effective date

[PlsHlpKat]

I have a table with EmpID, Name, Status, Payrate, Effective date, etc. showing employee status changes there is a new record for each change. I need to do two things with the table.
1. A report: I need to do a query to pull the record for each employee that has the latest <Eff date>.
2. A Form: I need to list the current payrate on a form which can also be pulled by the latest <Eff Date>.

Thanks

Kat

 

[Golom]

Try

Select EmpID, Name, Status, Payrate, EffectiveDate, ...

From tbl As T

Where T.EffectiveDate IN

      (Select MAX(EffectiveDate) From tbl As X
       Where X.EmpID = T.EmpID)

 

[PlsHlpKat]

My code is not working I am getting Syntax error am I doing something wrong?


SELECT[CLOCK #], [Last Name], [First Name], [Hire Date], Status, [EFF Date], [Review Date], Action, [Rating Code], [Increase Amt], [Inc Perct], [Pay Rate], [Next Review], [Department/Team], [Cost Center], [Job Code/Title], Supervisor, Code
FROM [Employee Performance Reviews] As T

Where T.[EFF Date] IN

(Select MAX(EFF Date) From[Employee Performance Reviews] As X Where X.CLOCK # = T.CLOCK #;