query expression

[exphtur]

I have an ASP script that connects to a Microsoft Access database and returns a payroll report. I have been able to get it to display totals of hours worked by hourly rate for each employee, but I want to be able to calculate time-and-a-half for hours worked in excess of 40 hours and double-time for hours worked in exces of 45, do I need to do this by creating an expression within the query ?

Thanks

webtoon

 

[SteveR77]

Yes, creating an expression in the query would probably be the easiest way to do this.

One way would be to use nested IIF statements as follows -

AdjustedPay: IIf([Hours]>45,([Hours]-45)*([Rate]*2),IIf([Hours]>40 And [Hours]<45,([Hours]-40)*([Rate]*1.5),[Hours]*[Rate]))

 

[BuGlen]

To add to Steve's excellent solution, you would also want to sum the values produced by the IIF statements to get the adjusted pay. For example:

AdjustedPay: [Rate]*IIf([Hours]>40,40,[Hours])+IIf([Hours]>40,([Rate]*1.5)*IIf([Hours]-40>5,5,([Hours]-40))+IIf([Hours]>45,([Rate]*2)*([Hours]-45),0),0)

Hope this helps.

Glen Appleton

VB.Net student.