Sum straight and overtime in single Access query

Question

I have an Access table containing timecard records for jobs:

JobID  HoursWorked  Rate
101    1.25
101    0.75         OT
102    0.33         DT
101    0.50
103    2.00

I want a query that returns a single record for each JobID that has summed the HoursWorked field. If [Rate] = "OT" then HoursWorked should be multiplied by 1.5, or in the case of DT, multiplied by 2.

The result would look like this:

JobID  TotalHoursWorked
101    2.875
102    0.66
103    2.00

I came up with this query which successfully SUMS the different types of Rate:

SELECT JobID, Sum(HoursWorked) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID, Rate;

But I'm stuck at how to multiply the different rates and return a single total by JobID.

Answer 1

Use a Switch expression to compute the multiplier for each Rate. Multiply HoursWorked by those values and Sum() it all up in a GROUP BY

SELECT
    t.JobID,
    Sum(t.HoursWorked * Switch(t.Rate='OT',1.5,t.Rate='DT',2,True,1)) AS TotalHoursWorked
FROM Timecards AS t
GROUP BY t.JobID;

That query returned your expected result when tested with your data in Access 2010.

Here is the Switch expression broken out and formatted. It returns the value from the first expression/value pair where the expression evaluates as True, and ignores any remaining pairs. The final expression/value pair (True, 1) returns 1 for any Rate which does not match 'OT' or 'DT':

Switch(
        t.Rate='OT', 1.5,
        t.Rate='DT', 2,
        True, 1
    )