Help with query calculation

[Craig McLaughlin]

I need a calculation to determine holiday pay.

I have the total hrs work*5.6/52 is the calcuation I use. HOWEVER if the
person works more than 39 hrs a week those hours have to be ignored.

So if someone worked say 50hrs how would I do the same calculation but
ignoring all hours worked beyond 39?

Thanks

Craig

 

[Craig McLaughlin]

finally solved it using the IIF function

 

[Krzysztof Naworyta]

Craig McLaughlin wrote:
| I need a calculation to determine holiday pay.
|
| I have the total hrs work*5.6/52 is the calcuation I use. HOWEVER if
| the person works more than 39 hrs a week those hours have to be
| ignored.
|
| So if someone worked say 50hrs how would I do the same calculation but
| ignoring all hours worked beyond 39?

first query (qr1):

SELECT
format(date1,"mm") & Format(DatePart("ww",date1,0,0),"00") AS month_week
, Sum(hours) AS total
FROM Table1
GROUP BY
format(date1,"mm") & Format(DatePart("ww",date1,0,0),"00")

2. qr:

SELECT
sum(IIf(total>39,39,total)) as tot_work_hours
FROM
qr1