1

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.

I say Reinstate Monica
  • 458
  • 10
  • 27

3 Answers3

4

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
    )
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Looks better than my nested iifs :) I don't use Access enough to remember the finer points I guess. – jpw Apr 27 '15 at 21:47
  • 2
    `Switch` doesn't get enough attention. I think your nested `IIf` logic was correct, so I upvoted. But, for me, `Switch` is easier to understand and manage. See another example at [What is the equivalent of Select Case in Access SQL?](http://stackoverflow.com/a/15776608/77335) – HansUp Apr 27 '15 at 21:53
2

You can use the IIF function to conditionally determine what to aggregate:

SELECT 
  JobID, 
  SUM(IIF(rate = 'OT', HoursWorked * 1.5, IIF(rate = 'DT' ,HoursWorked * 2.0, HoursWorked))) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID;

The logic is iif(boolean test, value if true, value if false)

Since you have two conditions to test you have to nest the iif-expressions so if rate = 'OT' then multiply by 1,5, else check if rate = 'DT' and if so multiply with 2, or use the value as is if rate is something else.

Also, when you use aggregate functions you should not group by the column(s) you aggregate. If you do you'll most likely not get the result you want.

jpw
  • 44,361
  • 6
  • 66
  • 86
-1

try this one: 

SELECT 
    JobID, 
    Sum(HoursWorked * CASE 
                        WHEN rate = 'OT' THEN 1.5 
                        WHEN rate = 'DT' THEN 2
                        ELSE 1
                    END
        ) AS TotalHoursWorked
FROM Timecards
GROUP BY JobID
Matija Gluhak
  • 260
  • 2
  • 13