Group By Hour, Avearge Multiple Shift Types within Hour

[athyeh]

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%

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

[mrojas]

How do users select a Shift Type? Is there input form? How is it that selecting Break under one hour runs over into the next?