By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,461 Members | 1,108 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,461 IT Pros & Developers. It's quick & easy.

How do I calculate Overtime hours

P: 24
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.
Jul 19 '10 #1

✓ answered by 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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [subMH].[Date By Day]
  2.        , tE.LastName
  3.        , tE.FirstName
  4.        , tE.CraftCode
  5.        , tE.Classification
  6.        , tE.STRate
  7.        , tE.OTRate
  8.        , tE.PerDiem
  9.        , [subMH].[Sum Of Time]
  10.        , [STRate]*1.1871 AS ReimSTRate
  11.        , [subMH].[Sum Of Time]*[ReimSTRate]+
  12.          IIf([subMH].[Sum Of Time]>40,
  13.              ([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),
  14.              0) AS [DailyRate]
  15.  
  16. FROM     Employees AS [tE] INNER JOIN
  17.     (
  18.     SELECT    [Employee]
  19.             ,  Format([Date],'Short Date') AS [Date By Day]
  20.             ,  Sum([Time]) AS [Sum Of Time]
  21.      FROM     [CEP ManHours]
  22.      GROUP BY [Employee]
  23.             , [Date]
  24.     ) AS [subMH]
  25.   ON     [subMH].Employee=[tE].LastName

Share this Question
Share on Google+
26 Replies


P: 6
Could you do some VBA code in the background?

Expand|Select|Wrap|Line Numbers
  1. totalpay = totalhours * payrate
  2.  
  3. If (totalhours - 40) > 0 Then
  4.   totalpay = totalpay + ((totalhours - 40) * payrate * .5)
  5. Endif
Jul 19 '10 #2

NeoPa
Expert Mod 15k+
P: 31,768
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 :)
Jul 20 '10 #3

P: 6
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
Jul 20 '10 #4

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 20 '10 #5

P: 6
Oh, I understand!
Jul 20 '10 #6

P: 24
@NeoPa
As you stated below, straight time is 40 hours and overtime is 1.5 * any hours over straight time
Jul 21 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
Thank you Bill. So can we assume SixDonut's code worked for you?
Jul 22 '10 #8

P: 24
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.
Jul 22 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. Sum([Hours])*[Rate]+IIf(Sum([Hours])>40,(Sum([Hours])-40)*[Rate]/2),0) AS [Due]
Jul 22 '10 #10

P: 24
My Rate is ReimSTRate (Reimbursable STraight Rate) When I attempt the Sum etc. stated above I get an invalid syntax error.
Jul 22 '10 #11

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 22 '10 #12

P: 24
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. DailyCost = Time * (STRate * 1.1871)
  4.  
  5. If (Time - 40) > 0 Then
  6.   DailyCost = DailyCost + (((STRate * 1.1871) - 40) * (STRate * 1.1871) * 0.5)
  7. End If
Jul 23 '10 #13

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 23 '10 #14

P: 24
@NeoPa
lol... that could be a reason as to why it isn't working... I'll post it up here in a few minutes
Jul 23 '10 #15

P: 24
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT   DISTINCTROW
  2.          Format$([CEP ManHours].Date,'Short Date') AS [Date By Day]
  3.        , Employees.LastName
  4.        , Employees.FirstName
  5.        , Employees.CraftCode
  6.        , Employees.Classification
  7.        , Employees.STRate
  8.        , Employees.OTRate
  9.        , Employees.PerDiem
  10.        , Sum([CEP ManHours].Time) AS [Sum Of Time]
  11.        , [STRate]*1.1871 AS ReimSTRate
  12.        , ([ReimSTRate]*[Sum Of Time])+[PerDiem] AS DailyRate
  13.  
  14. FROM     [CEP ManHours] INNER JOIN
  15.          Employees
  16.   ON     [CEP ManHours].Employee = Employees.LastName
  17.  
  18. GROUP BY Format$([CEP ManHours].Date,'Short Date')
  19.        , Employees.LastName
  20.        , Employees.FirstName
  21.        , Employees.CraftCode
  22.        , Employees.Classification
  23.        , Employees.STRate
  24.        , Employees.OTRate
  25.        , Employees.PerDiem
  26.        , [STRate]*1.1871
  27.        , ([ReimSTRate]*[Sum Of Time])+[PerDiem];
Jul 27 '10 #16

NeoPa
Expert Mod 15k+
P: 31,768
In a case like this I'd do a subquery on the [CEP ManHours] table and link that in :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [subMH].[Date By Day]
  2.        , tE.LastName
  3.        , tE.FirstName
  4.        , tE.CraftCode
  5.        , tE.Classification
  6.        , tE.STRate
  7.        , tE.OTRate
  8.        , tE.PerDiem
  9.        , [subMH].[Sum Of Time]
  10.        , [STRate]*1.1871 AS ReimSTRate
  11.        , [subMH].[Sum Of Time]*[ReimSTRate]+
  12.          IIf([subMH].[Sum Of Time]>40,
  13.              ([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),
  14.              0) AS [DailyRate]
  15.  
  16. FROM     Employees AS [tE] INNER JOIN
  17.     (
  18.     SELECT    [Employee]
  19.             ,  Format([Date],'Short Date') AS [Date By Day]
  20.             ,  Sum([Time]) AS [Sum Of Time]
  21.      FROM     [CEP ManHours]
  22.      GROUP BY [Date]
  23.     ) AS [subMH]
  24.   ON     [subMH].Employee=[tE].LastName
Jul 28 '10 #17

P: 24
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."
Jul 28 '10 #18

NeoPa
Expert Mod 15k+
P: 31,768
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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [subMH].[Date By Day]
  2.        , tE.LastName
  3.        , tE.FirstName
  4.        , tE.CraftCode
  5.        , tE.Classification
  6.        , tE.STRate
  7.        , tE.OTRate
  8.        , tE.PerDiem
  9.        , [subMH].[Sum Of Time]
  10.        , [STRate]*1.1871 AS ReimSTRate
  11.        , [subMH].[Sum Of Time]*[ReimSTRate]+
  12.          IIf([subMH].[Sum Of Time]>40,
  13.              ([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),
  14.              0) AS [DailyRate]
  15.  
  16. FROM     Employees AS [tE] INNER JOIN
  17.     (
  18.     SELECT    [Employee]
  19.             ,  Format([Date],'Short Date') AS [Date By Day]
  20.             ,  Sum([Time]) AS [Sum Of Time]
  21.      FROM     [CEP ManHours]
  22.      GROUP BY [Employee]
  23.             , [Date]
  24.     ) AS [subMH]
  25.   ON     [subMH].Employee=[tE].LastName
Jul 28 '10 #19

P: 24
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!!!!
Jul 28 '10 #20

NeoPa
Expert Mod 15k+
P: 31,768
Very kind of you to say so Bill :)

I'm also interested to know what minor amendments were required, if you can share that.
Jul 28 '10 #21

P: 24
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)
Expand|Select|Wrap|Line Numbers
  1. subMH].[Sum Of Time]*[ReimSTRate]+IIf([subMH].[Sum Of Time]>40,([subMH].[Sum Of Time]-40)*[ReimSTRate]/2),0) AS DailyRate
Jul 28 '10 #22

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 29 '10 #23

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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Employees AS tE INNER JOIN [SELECT    [Employee] 
  3.             ,  Format([Date],'Short Date') AS [Date By Day] 
  4.             ,  Sum([Time]) AS [Sum Of Time] 
  5.      FROM     [CEP ManHours] 
  6.      GROUP BY [Employee] 
  7.             , [Date] 
  8.     ]. AS subMH ON tE.LastName = subMH.Employee;
  9.  
And the error is "Extra ) in query expression '0)'
Aug 9 '10 #24

NeoPa
Expert Mod 15k+
P: 31,768
Your expression for [DailyRate (Line #1) is :
Expand|Select|Wrap|Line Numbers
  1. [subMH].[Sum Of Time]*[ReimSTRate]+IIf([subMH].[Sum Of Time]>40,([subMH].[Sum Of Time]-40)*[ReimSTRate]/2), 0) AS DailyRate
It should be :
Expand|Select|Wrap|Line Numbers
  1. [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 :
Expand|Select|Wrap|Line Numbers
  1. INNER JOIN [SELECT
to :
Expand|Select|Wrap|Line Numbers
  1. INNER JOIN (SELECT
and :
Expand|Select|Wrap|Line Numbers
  1. ]. AS subMH ON
to :
Expand|Select|Wrap|Line Numbers
  1. ) AS subMH ON
Aug 9 '10 #25

P: 24
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!!!
Aug 11 '10 #26

NeoPa
Expert Mod 15k+
P: 31,768
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.
Aug 11 '10 #27

Post your reply

Sign in to post your reply or Sign up for a free account.