455,611 Members | 1,449 Online
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

# Calculating overtime

 P: 30 I am using an access database to keep track of employees times. I have spent several months building this and it all works great, except I need to be able to calculate overtime hours on the fly. The tables I have are tblTIMES, tblEMPLOYEES, and tblHOLIDAYS. I need to calculate the hours worked in a week with anything over 40 hours being overtime unless there is a holiday or vacation time. All of my time records in the tblTIMES table have a timetype field(N=Normal, H=Holiday, V=Vacation, etc...). I hope I have explained this with enough detail. Nov 2 '11 #1

Very interesting. Looking at it I can see immediately why it's wrong. Getting it right is not so easy, but I think I have it as I think I can assume that [ClockOut] and [ClockIn] can now be treated as DateTime fields rather than simply times. It should read (but please check and test to make sure) :

Expand|Select|Wrap|Line Numbers
1. SELECT   [EmployeeNo]
2.        , DateDiff('d',[Enter Start Date],[ClockIn]) \ 7 AS [WeekNo]
3.        , DateAdd('ww',[WeekNo],[Enter Start Date]) AS [WeekStart]
4.        , Sum(HoursAndMinutes([ClockOut]-[ClockIn])) AS [TotalHours]
5.        , IIf([TotalHours] > 40,[TotalHours] - 40,0) AS [OvertimeHours]
6.        , [TotalHours] - [OvertimeHours] AS [StandardHours]
7. FROM     [tblTimes]
8. WHERE    ([TimeType] = 'N')
9.   AND    ([ClockIn] Between [Enter Start Date] And [Enter End Date])
10. GROUP BY [EmployeeNo]
11.        , DateDiff('d',[Enter Start Date],[ClockIn]) \ 7
PS. I highlighted the changes from your code to facilitate comprehension of the purpose of the change.

10 Replies

 Expert Mod 15k+ P: 31,711 Not really Wesley. There is a large amount of info missing for this to make sense and be properly answerable (without some of our number posting after guessing what you mean). You are working with field data, yet you give us only table names. What fields are you working with and how do they all fit together? Nov 2 '11 #2

 P: 30 Thanks for the response NeoPa!! Ok. Here it goes. The tblTIMES table has the following fields: [TimeID] as AutoNumber, [EmployeeNo] as Number, [ClockInDate] as Date/Time, [TimeIn] as Date/Time, [ClockOutDate] as Date/Time, [TimeOut] as Date/Time, [TimeType] as Text, [TimeComments] as Text, and [CurStatus] as Text. I use a function called HoursAndMinutes() to calculate the time difference between the [TimeOut] and [TimeIn] fields and format it into a decimal number (e.g. 7.5 = 7 hours and 30 minutes). Each Clock In/Out Pair is stored as one record in the tblTIMES Table. I then run reports with the start dates and end dates varying , depending on the time period selected, which lists each record for that period([ClockInDate],[TimeIn], [ClockOutDate], [TimeOut], [TimeType], and [Total Hours] Which is calculated using the aforementioned function "HoursAndMinutes()"). I need to be able to determine which records are in a specified week, determine if overtime pay is needed, and calculate the total hours of overtime. Overtime is paid when an employee works more than 40 hours per week in which there are no vacation days or holidays. Each record in the tblTIMES table has a [TimeType] Field that specifies it as Normal time, Vacation, Holiday , or Other. The tblEMPLOYEES table holds information about each employee (Name, EmployeeNo, Birth Date, Hire Date, etc.) Hope this helps! Nov 3 '11 #3

 P: 30 By the way I am using Access 2010 ;) I just read your "How to ask good Questions" Thread. Nov 3 '11 #4

 Expert Mod 15k+ P: 31,711 Firstly I should apologise for my absence - so sorry for that. Next up is to say that your post #3 is much better an explanation than was included in the OP (Post #1). If you remember this for future questions you will notice much better responses (You'll nearly always get better responses than for this one anyway I suppose, as I went and left you for a week, but you get my point I'm sure). Now to the matter in hand. There are a number of points worth raising, but I think at this stage I can introduce concepts that will get you going. It's rare that splitting dates and times ([ClockInDate] & [TimeIn] as well as [ClockOutDate] & [TimeOut]) is a good idea. I see no benefit to your setup here. Quite the contrary - it makes the logic much more convoluted to implement. You don't specify any inbuilt assumptions of your logic, but I'm guessing from your data layout that you're working on the basis that the starting date of any time record is considered the date of that record. IE. Any record starting in one day is considered to represent time within that day and, by extension, that any record starting within a week is considered to represent time within that week. Otherwise, again, life gets more complicated. If you need to be told, then [Total Hours], as a calculated field, should not be stored in your database but worked out on the fly every time it's required. Clearly this is less straightforward with your current structure, but true in whichever way you hold your data. If you have any changes to make to your explanation of the situation then do so. I'll proceed anyway on this basis until I hear otherwise. At this stage, assessing the amount of hours worked in a week seems pretty straightforward : Expand|Select|Wrap|Line Numbers SELECT   [EmployeeNo]        , [Enter Week Start] AS [WeekStart]        , Sum(HoursAndMinutes([ClockInDate], [TimeIn], [ClockOutDate], [TimeOut])) AS [TotalHours]        , IIf([TotalHours] > 40,[TotalHours] - 40,0) AS [OvertimeHours]        , [TotalHours] - [OvertimeHours] AS [StandardHours] FROM     [tblTimes] WHERE    ([TimeType] = 'N')   AND    ([ClockInDate] Between [Enter Week Start] And DateAdd('d', 6, [Enter Week Start])) GROUP BY [EmployeeNo] Nov 11 '11 #5

 P: 30 Thanks for the response. I have been trying to heed your advice by storing my date/time fields together. At the point my database was previously at, I needed to change tables, forms, queries, and most importantly CODE to implement this. In doing so, I have run into a new problem. It does not involve the current "Calculating Overtime" problem, so I will start a new post. Second, I AM considering the start date to represent the time for that day, as we should never have a time intentionally cross midnight. If that happened, it would be because an employee forgot to clock out for the day. However, I do not quite understand why "life gets more complicated" because of this, or what other ways of storing dates for each time record I can use. Third, [Total Hours] is not a stored field. It is calculated in queries and in code when needed. I also see a potential problem with the presented code. My "Start Date" and "End Date" for the reports that I will be running will most certainly span two or more weeks. So if [Total Hours] is greater than 40, this does not necessarily mean that OT will be paid. IE. If the employee works 42 hours in the first week, and 46 in the second week, AND there is no time other than "Normal". The total overtime hours should be 8 not 48. Expand|Select|Wrap|Line Numbers IIf([TotalHours] >= 40,[TotalHours] - 40,0) AS [OvertimeHours] And if I understand your code correctly the [Total Hours] calculated would be 88 thus returning 48 as [Overtime Hours]. Hope that makes sense. Nov 19 '11 #6

 Expert Mod 15k+ P: 31,711 New thread. Good idea. Definitely what you need to do for any new questions. You talk of a 'start date' representing the time for a day, then go on to get confused about how to store it. If you remember that a time is not a date, but can be considered to have a date element, then a standard Date/Time field is how to store both the date part and the time part of this point in time. If you try to store the date separately from the time (simply because humans typically think that way) then you will condemn yourself to having to add the two disparate items together simply to end up with the single Date/Time value of your point in time. Thus everything gets more, and unnecessarily, complicated. I'm pleased to hear that. It shows good understanding. If you want to handle periods that do not match (NB. I didn't simply say exceed) working weeks, then you will need an inner (or sub-) query that first calculates the values for a proper working week first (Surely this is very obvious - Am I missing something deeper in the question?). Overtime is defined within the confines of a working week. Trying to calculate it without reference to that working week makes fundamentally no sense at all (Hence the totally spurious example results you offered as example). PS. I changed the code in post #5 slightly. Lines #4 and #8 are both changed. Nov 19 '11 #7

 P: 30 What you have described in #4 of the above reply is where the root of my original problem lies. I understand how to figure the Overtime hours for a single week. Where I get lost is how to create the "Inner Query", or the logic to label a specific set of dates to be considered part of one working week and a completely different set of dates to be part of a different working week. All the while knowing someone may want to run this report for a period covering six months... Maybe I am over thinking it. Nov 22 '11 #8

 Expert Mod 15k+ P: 31,711 That's certainly more compicated, but the following should be a good point to start from : Expand|Select|Wrap|Line Numbers SELECT   [EmployeeNo]        , DateDiff('d',[Enter Start Date],[Enter End Date]) \ 7 AS [WeekNo]        , DateAdd('ww',[WeekNo],[Enter Start Date]) AS [WeekStart]        , Sum(HoursAndMinutes([ClockInDate], [TimeIn], [ClockOutDate], [TimeOut])) AS [TotalHours]        , IIf([TotalHours] > 40,[TotalHours] - 40,0) AS [OvertimeHours]        , [TotalHours] - [OvertimeHours] AS [StandardHours] FROM     [tblTimes] WHERE    ([TimeType] = 'N')   AND    ([ClockInDate] Between [Enter Start Date] And [Enter End Date]) GROUP BY [EmployeeNo]        , DateDiff('d',[Enter Start Date],[Enter End Date]) \ 7 Nov 23 '11 #9

P: 30
I see how this should work. However, when I use the provided code, the query returns only totals from the last [WeekNo]. Instead of totals from each separate [WeekNo]. See attached for an example.

Oh and FYI, the code below is modified just a little bit on line #4 to work.

Expand|Select|Wrap|Line Numbers
1. SELECT   [EmployeeNo]
2.        , DateDiff('d',[Enter Start Date],[Enter End Date]) \ 7 AS [WeekNo]
3.        , DateAdd('ww',[WeekNo],[Enter Start Date]) AS [WeekStart]
4.        , Sum(HoursAndMinutes([ClockOut]-[ClockIn])) AS [TotalHours]
5.        , IIf([TotalHours] > 40,[TotalHours] - 40,0) AS [OvertimeHours]
6.        , [TotalHours] - [OvertimeHours] AS [StandardHours]
7. FROM     [tblTimes]
8. WHERE    ([TimeType] = 'N')
9.   AND    ([ClockIn] Between [Enter Start Date] And [Enter End Date])
10. GROUP BY [EmployeeNo]
11.        , DateDiff('d',[Enter Start Date],[Enter End Date]) \ 7

Attached Images
 OTHoursQuery.jpg (62.0 KB, 2296 views)
Nov 29 '11 #10

 Expert Mod 15k+ P: 31,711 Very interesting. Looking at it I can see immediately why it's wrong. Getting it right is not so easy, but I think I have it as I think I can assume that [ClockOut] and [ClockIn] can now be treated as DateTime fields rather than simply times. It should read (but please check and test to make sure) : Expand|Select|Wrap|Line Numbers SELECT   [EmployeeNo]        , DateDiff('d',[Enter Start Date],[ClockIn]) \ 7 AS [WeekNo]        , DateAdd('ww',[WeekNo],[Enter Start Date]) AS [WeekStart]        , Sum(HoursAndMinutes([ClockOut]-[ClockIn])) AS [TotalHours]        , IIf([TotalHours] > 40,[TotalHours] - 40,0) AS [OvertimeHours]        , [TotalHours] - [OvertimeHours] AS [StandardHours] FROM     [tblTimes] WHERE    ([TimeType] = 'N')   AND    ([ClockIn] Between [Enter Start Date] And [Enter End Date]) GROUP BY [EmployeeNo]        , DateDiff('d',[Enter Start Date],[ClockIn]) \ 7 PS. I highlighted the changes from your code to facilitate comprehension of the purpose of the change. Nov 30 '11 #11