i have access table containing timecard records jobs:
jobid hoursworked rate 101 1.25 101 0.75 ot 102 0.33 dt 101 0.50 103 2.00
i want query returns single record each jobid has summed hoursworked field. if [rate] = "ot" hoursworked should multiplied 1.5, or in case of dt, multiplied 2.
the result this:
jobid totalhoursworked 101 2.875 102 0.66 103 2.00
i came query sums different types of rate:
select jobid, sum(hoursworked) totalhoursworked timecards group jobid, rate; but i'm stuck @ how multiply different rates , return single total jobid.
use switch expression compute multiplier each rate. multiply hoursworked values , sum() in group by
select t.jobid, sum(t.hoursworked * switch(t.rate='ot',1.5,t.rate='dt',2,true,1)) totalhoursworked timecards t group t.jobid; that query returned expected result when tested data in access 2010.
here switch expression broken out , formatted. returns value first expression/value pair expression evaluates true, , ignores remaining pairs. final expression/value pair (true, 1) returns 1 rate not match 'ot' or 'dt':
switch( t.rate='ot', 1.5, t.rate='dt', 2, true, 1 ) 