Performance Query 1

[Sussex]

I have been tasked with creating performance reports. We are tracking performance of work completed vs. assigned by user id. I have gotten to the point of being able to generate a percentage by simple math. The part where I am stuck is they want to go back just the last twenty shifts and if that user was below 94% four times output them to a report. The fields in my query are userid, date, sumofassigned, sumofcompleted, and percent. Each date represents one shift. I can not go by date because people do not work everyday. What would be the best way to accomplish this? I know I am missing something.

Thanks

 

[ProgramError]

There are a couple of things to thnk about here.
What field determines how far you go back, is there a field which logs the shift each worker has worked on and in what format are they stored?
Try using the 'top values' icon in the menu bar to show only the latest records after a sort on the shift criteria

Hope this ponts you in the right direction

Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[Sussex]

Thanks ProgramError. Actually the date field determines the shift. The problem with using the date is I can't just go back 20 days since a worker may not have worked 20 consecutive days. My fields in the percentage calcualte query are, userid, sumofassigned, sumofcompleted, percent (which is assigned/completed) and date.

Is there a way to take this query and write another one to go back the 20 shifts and find which ones have hit the 94% or less four times out of the last twenty? I know I am missing something, please help.

Thanks

 

[dhookom]

To get the most recent 20 records for each UserID, try a query like:

SELECT [percentage calcualte].*
FROM [percentage calcualte]
WHERE [Date] In (SELECT TOP 20 [Date] FROM [percentage calcualte] pc WHERE pc.UserID = [percentage calcualte].UserID ORDER BY [Date] DESC);

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Sussex]

Thanks dhookom and ProgramError works like a charm!