MAX Statement 4

[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>.

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 #;

Thanks

Kat

 

[jrbarnett]

Hi

This query will pull the latest EFF Date for each employee, so can be used as the recordsource for your report:

SELECT [CLOCK #], [Last Name], [First Name], [Hire Date], Status, Max ([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
GROUP BY CLOCK #], [Last Name], [First Name], [Hire Date], Status, [Review Date], Action, [Rating Code], [Increase Amt], [Inc Perct], [Pay Rate], [Next Review], [Department/Team], [Cost Center], [Job Code/Title], Supervisor, Code

This will also pull the pay rate off at the latest value, so can be used for the form in a read only manner, but this won't be able to be edited because of the Group by statement.

John

 

[hneal98]

Yes. You don't have a closing ) for the select query in the where clause.

 

[PlsHlpKat]

I copied your code word for work and I am still getting all records???

Kat

 

[Golom]

You may need to go back to the first construct. You are getting all records in the GROUP BY because one or more of the fields in the grouped fields have different values. Try this

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 #])

I've added [] around 'EFF Date', 'CLOCK #' and a ) at the end and inserted spaces where needed.

 

[PlsHlpKat]

Thank you golom!

This works great!!

Kat

 

[byurow]

Hey Golom,

Thanks for the great code! You just helped me solve a pretty big problem without my having to ask a single question! Have a star!

Brenda

 

[vandaliarental]

Thanks, Golom! Just what I was looking for!

Have a star!

Connie