I used this query (similar to Joe's suggestion) to get actualHours
Code:
SELECT costcenter
, IIF(([PayCode]="REG") OR ([PayCode]="OTS"),[HrsPaid],0) AS ActualHours
, Paycode
, EarningsAmount
FROM Costcenter2;
which gives this as data
Code:
costcenter |
ActualHours |
Paycode |
EarningsAmount |
20530 |
7.5 |
REG |
190.37 |
20530 |
0 |
HOD |
163.17 |
20530 |
0 |
HOD |
122.38 |
20530 |
0 |
OTL |
14.5 |
20530 |
0 |
OTL |
65.8 |
20530 |
1.14 |
OTS |
29.01 |
20530 |
4.86 |
OTS |
123.28 |
20530 |
17.14 |
REG |
435.12 |
80856 |
0 |
HOD |
290.35 |
80856 |
0 |
HOD |
217.76 |
80856 |
0 |
OTL |
131.73 |
80856 |
0 |
OTL |
98.8 |
80856 |
4.285809 |
OTS |
193.57 |
80856 |
3.214191 |
OTS |
145.17 |
80856 |
17.142903 |
REG |
774.27 |
80856 |
50.357097 |
REG |
2 |
Then this query, using the above query, to do sums and grouping.
Code:
SELECT costcenter_frst.costcenter
, Sum(costcenter_frst.ActualHours) AS SumOfActualHours
, Sum(costcenter_frst.EarningsAmount) AS SumOfEarningsAmount
FROM costcenter_frst
GROUP BY
costcenter_frst.costcenter;
To get this data
Code:
costcenter |
SumOfActualHours |
SumOfEarningsAmount |
20530 |
30.64 |
1143.63 |
80856 |
75 |
1853.65 |
Hope it is useful. (and that any assumptions and data mods are in line with your needs)