How do I calculate Overtime hours

[Bill Sublette]

Okay so here's situation. I have an Access 2003 Report that builds off of a Query which pulls from a Table which is populated by a form... The data is the employee (name), the hours they worked for the day, and the project they worked. I have a Start Date and and End Date Parameter set up. I am trying to create a Weekly and a Monthly Report for cost. I need it(query maybe?) to add up all the hours worked by that employee, calculate the overtime and straight time hours, multiply those by the billing rate and then sum up the total cost for the week or month depending on the report being generated. HELP!!!! I have tried all kinds of approaches and formulas and can't get anything to work right.

[sixdonuts]

Could you do some VBA code in the background?

totalpay = totalhours * payrate
 
If (totalhours - 40) > 0 Then
  totalpay = totalpay + ((totalhours - 40) * payrate * .5)
Endif

[NeoPa]

I think you'd need to start by posting what the actual logic is of what is normal, and what overtime work.

SixDonuts has posted some code that gives you a big clue as to how you can proceed, but he was working blind as to the logic.

Welcome to Bytes! Both of you :)

[sixdonuts]

Nothing is wrong with my logic!

totalhours * payrate + hours above 40 * 1/2 payrate

it's the same as

first 40 hours * payrate + hours above 40 * 3/2 payrate

[NeoPa]

I wasn't suggesting there was anything wrong with your logic. Simply that as you have no way of knowing what the logic should be (as that is only known to the OP and not yet shared) the logic in what you posted should not be relied upon to match that in the OP's situation.

IE. Your code assumes basic hours of 40 per week and an overtime rate of 1.5 * normal rate. This is information only the OP can provide reliably. As I said though, your post indicates the sort of thing needed to handle his problem as long as you don't rely on these figures in the logic.

[sixdonuts]

Oh, I understand!

[Bill Sublette]

@NeoPa
As you stated below, straight time is 40 hours and overtime is 1.5 * any hours over straight time

[NeoPa]

Thank you Bill. So can we assume SixDonut's code worked for you?

[Bill Sublette]

I'm not sure I am putting the code in the right place unfortunately... I opened up my query and then used the macro shortcut (in the Tools menu dropdown) and changed the names to reflect mine. I even tried associating it to a table and it still didn't do anything.

[NeoPa]

Grouping can either be done in the query or the report. You would need to group by the employee and the date grouping (Week/month whatever). You also need a [Rate] field available (This apparently not, according to your first post).
In your query you'd need an extra field something like :

Sum([Hours])*[Rate]+IIf(Sum([Hours])>40,(Sum([Hours])-40)*[Rate]/2),0) AS [Due]

[Bill Sublette]

My Rate is ReimSTRate (Reimbursable STraight Rate) When I attempt the Sum etc. stated above I get an invalid syntax error.

[NeoPa]

Why don't you post the SQL you actually used and I'll take a look at it for you. There is far too little info available to me otherwise.

[Bill Sublette]

Option Compare Database
 
DailyCost = Time * (STRate * 1.1871)
 
If (Time - 40) > 0 Then
  DailyCost = DailyCost + (((STRate * 1.1871) - 40) * (STRate * 1.1871) * 0.5)
End If

[NeoPa]

We appear to have a problem Houston!

This is not SQL code. It is sort of VBA code, but it will never run as it is not in any procedure.

SQL code is what is within a QueryDef (saved Access query). If you open your QueryDef in design view you will have the option in the View menu to View SQL. This is where you need to be looking to effect what you need.

[Bill Sublette]

@NeoPa
lol... that could be a reason as to why it isn't working... I'll post it up here in a few minutes

[Bill Sublette]

Sorry it's been a while since I sent anything but I had to use a backup program (I totally messed up the original)... here is the SQL View.

SELECT   DISTINCTROW
         Format$([CEP ManHours].Date,'Short Date') AS [Date By Day]
       , Employees.LastName
       , Employees.FirstName
       , Employees.CraftCode
       , Employees.Classification
       , Employees.STRate
       , Employees.OTRate
       , Employees.PerDiem
       , Sum([CEP ManHours].Time) AS [Sum Of Time]
       , [STRate]*1.1871 AS ReimSTRate
       , ([ReimSTRate]*[Sum Of Time])+[PerDiem] AS DailyRate
 
FROM     [CEP ManHours] INNER JOIN
         Employees
  ON     [CEP ManHours].Employee = Employees.LastName
 
GROUP BY Format$([CEP ManHours].Date,'Short Date')
       , Employees.LastName
       , Employees.FirstName
       , Employees.CraftCode
       , Employees.Classification
       , Employees.STRate
       , Employees.OTRate
       , Employees.PerDiem
       , [STRate]*1.1871
       , ([ReimSTRate]*[Sum Of Time])+[PerDiem];

[NeoPa]

In a case like this I'd do a subquery on the [CEP ManHours] table and link that in :

SELECT   [subMH].[Date By Day]
       , tE.LastName
       , tE.FirstName
       , tE.CraftCode
       , tE.Classification
       , tE.STRate
       , tE.OTRate
       , tE.PerDiem
       , [subMH].[Sum Of Time]
       , [STRate]*1.1871 AS ReimSTRate
       , [subMH].[Sum Of Time]*[ReimSTRate]+
         IIf([subMH].[Sum Of Time]>40,
             ([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),
             0) AS [DailyRate]
 
FROM     Employees AS [tE] INNER JOIN
    (
    SELECT    [Employee]
            ,  Format([Date],'Short Date') AS [Date By Day]
            ,  Sum([Time]) AS [Sum Of Time]
     FROM     [CEP ManHours]
     GROUP BY [Date]
    ) AS [subMH]
  ON     [subMH].Employee=[tE].LastName

[Bill Sublette]

When I tried that I received the error "You tried to execute a query that does not include the specified expression 'Employee' as part of an aggregate function."

[NeoPa]

You would. There's an error in there (Sorry - I'm unable to test without your database so working entirely in my head). Try this instead (The change starts at line #22) :

SELECT   [subMH].[Date By Day]
       , tE.LastName
       , tE.FirstName
       , tE.CraftCode
       , tE.Classification
       , tE.STRate
       , tE.OTRate
       , tE.PerDiem
       , [subMH].[Sum Of Time]
       , [STRate]*1.1871 AS ReimSTRate
       , [subMH].[Sum Of Time]*[ReimSTRate]+
         IIf([subMH].[Sum Of Time]>40,
             ([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),
             0) AS [DailyRate]
 
FROM     Employees AS [tE] INNER JOIN
    (
    SELECT    [Employee]
            ,  Format([Date],'Short Date') AS [Date By Day]
            ,  Sum([Time]) AS [Sum Of Time]
     FROM     [CEP ManHours]
     GROUP BY [Employee]
            , [Date]
    ) AS [subMH]
  ON     [subMH].Employee=[tE].LastName

[Bill Sublette]

By Joe I think You've got it!!!!!!!!! I had to make a very minor repair but as you said you were doing it from your head and here I am with all my tables, the internet, AND Access 2003 For Dummies and couldn't get it!!! YOU ARE AWESOME!!!!

[NeoPa]

Very kind of you to say so Bill :)

I'm also interested to know what minor amendments were required, if you can share that.

[Bill Sublette]

Hmmm.... the part I seem to be having an issue with is if there isn't overtime then it's not putting the total in the DailyTotal column... I think it has something to do with the error I keep getting about the extra ) in the expression 0)

subMH].[Sum Of Time]*[ReimSTRate]+IIf([subMH].[Sum Of Time]>40,([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),0) AS DailyRate

[NeoPa]

Why don't you post the whole SQL and the exact error message. I can't do much with a snippet of code without context I'm afraid. Too many unanswered questions.

[Bill Sublette]

Sorry it's been so long since I responded... I've been knee deep in 20K+ drawings/schematics... here is the code.

SELECT subMH.[Date By Day], tE.LastName, tE.FirstName, tE.CraftCode, tE.Classification, tE.STRate, tE.OTRate, tE.PerDiem, subMH.[Sum Of Time], [STRate]*1.1871 AS ReimSTRate, [subMH].[Sum Of Time]*[ReimSTRate]+IIf([subMH].[Sum Of Time]>40,([subMH].[Sum Of Time]-40)*[ReimSTRate]/2), 0) AS DailyRate
FROM Employees AS tE INNER JOIN [SELECT    [Employee] 
            ,  Format([Date],'Short Date') AS [Date By Day] 
            ,  Sum([Time]) AS [Sum Of Time] 
     FROM     [CEP ManHours] 
     GROUP BY [Employee] 
            , [Date] 
    ]. AS subMH ON tE.LastName = subMH.Employee;
 

And the error is "Extra ) in query expression '0)'

[NeoPa]

Your expression for [DailyRate (Line #1) is :

[subMH].[Sum Of Time]*[ReimSTRate]+IIf([subMH].[Sum Of Time]>40,([subMH].[Sum Of Time]-40)*[ReimSTRate]/2), 0) AS DailyRate

It should be :

[subMH].[Sum Of Time]*[ReimSTRate]+IIf([subMH].[Sum Of Time]>40,([subMH].[Sum Of Time]-40)*[ReimSTRate]/2, 0) AS DailyRate

The extra, unwarranted, closing parenthesis is after the "/2".

Another problem is waiting to bite you if you make this change on your current SQL though - See Access QueryDefs Mis-save Subquery SQL. Essentially you need to change :

INNER JOIN [SELECT

to :

INNER JOIN (SELECT

and :

]. AS subMH ON

to :

) AS subMH ON

[Bill Sublette]

Holy Crapola if that didn't fix it all!!! I guarantee if I ever have a future problem with this stuff (which I'm sure I will) I'll be coming to byte.com... Thanks Neo!!!

[NeoPa]

Bill: Holy Crapola if that didn't fix it all!!!

I'm insulted that you should be surprised :D

J/K I'm pleased that helped.

Bill: I'll be coming to byte.com... Thanks Neo!!!

If you leave the "s" off then you'll miss :D When you tell all your friends, make sure to get the link right.