Hi Folks,
My current query is shown below, and it works great. Except for one fatal flaw.
Operators are selecting Shift Type at irregular periods throughout the hour, which creates two goals for the hour, instead of just one. Is there anyway to embed an update query (or some other mechanism) that IF an operator selects "Break" or "Lunch" during that hour, it updates all the shift type fields to "Break" or "Lunch" ONLY for that hour? (So if the Operator selects "Break" under [Shift Type] at Hour 17, Hour 17 Shift Types get updated to "Break" and it does not run over into Hour 18)
Date |
Operator |
Prod Hour |
Shift Type |
CountOfLID |
Goal |
Text15 |
09/20/2013 |
John Smith |
17 |
Break |
3 |
26 |
12% |
09/20/2013 |
John Smith |
17 |
Full Shift |
13 |
35 |
37% |
Code:
SELECT Format([Time Added],"mm/dd/yyyy") AS [Date], [D1-Prod-MainTable].Operator, Hour([Time Added]) AS [Prod Hour], [D1-Prod-MainTable].[Shift Type], Count([D1-Prod-MainTable].LID) AS CountOfLID, IIf([Shift Type]="Full Shift","35",IIf([Shift Type]="Break","26",IIf([Shift Type]="Lunch","18","35"))) AS Goal, DCount("[LID]","D1-Prod-MainTable","[LID]=" & [D1-Prod-MainTable].[LID])/[Goal] AS Expr1
FROM [D1-Prod-MainTable]
GROUP BY Format([Time Added],"mm/dd/yyyy"), [D1-Prod-MainTable].Operator, Hour([Time Added]), [D1-Prod-MainTable].[Shift Type], IIf([Shift Type]="Full Shift","35",IIf([Shift Type]="Break","26",IIf([Shift Type]="Lunch","18","35"))), DCount("[LID]","D1-Prod-MainTable","[LID]=" & [D1-Prod-MainTable].[LID])/[Goal]
HAVING (((Format([Time Added],"mm/dd/yyyy"))>=Format([Forms]![D1-Reports]![DateStart],"mm/dd/yyyy") And (Format([Time Added],"mm/dd/yyyy"))<=Format([Forms]![D1-Reports]![DateTo],"mm/dd/yyyy")) AND (([D1-Prod-MainTable].Operator)=[Forms]![D1-Reports]![Operator]));