459,461 Members | 1,108 Online
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

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

26 Replies

 P: 6 Could you do some VBA code in the background? Expand|Select|Wrap|Line Numbers totalpay = totalhours * payrate   If (totalhours - 40) > 0 Then   totalpay = totalpay + ((totalhours - 40) * payrate * .5) Endif Jul 19 '10 #2

 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

 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

 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

 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 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

 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 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 Jul 23 '10 #13

 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 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]; Jul 27 '10 #16

 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 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 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

 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 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 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

 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 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

 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 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)' Aug 9 '10 #24

 Expert Mod 15k+ P: 31,768 Your expression for [DailyRate (Line #1) is : Expand|Select|Wrap|Line Numbers [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 [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 INNER JOIN [SELECT to : Expand|Select|Wrap|Line Numbers INNER JOIN (SELECT and : Expand|Select|Wrap|Line Numbers ]. AS subMH ON to : Expand|Select|Wrap|Line Numbers ) 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

 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