Hello,
I have a report whose record source is based on the query below.
In my report I cannot figure out how to Sum([Goal]) or calculate an average based off [% of Goal].
*I am able to sum CountofLID through Format Ribbon, Grouping & Totals Menu, Sigma Totals Option, but the only selections available when I select [Goal] or [% of Goal] is Count Records, Count Values. I am guessing this is because [Goal] and [% of Goal] are calculated fields in the query?
Help is greatly appreciated.
Date |
Operator |
Prod Hour |
Shift Type |
CountOfLID |
Goal |
% of Goal |
AccessTotalsCountOfLID |
09/26/2013 |
John Smith |
11 |
Full Shift |
14 |
35 |
40% |
39 |
09/26/2013 |
John Smith |
12 |
Full Shift |
2 |
35 |
6% |
39 |
09/26/2013 |
John Smith |
12 |
Lunch |
8 |
18 |
44% |
39 |
09/26/2013 |
John Smith |
13 |
Break |
9 |
26 |
35% |
39 |
09/26/2013 |
John Smith |
13 |
Full Shift |
6 |
35 |
17% |
39 |
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
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")))
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]));