473,322 Members | 1,405 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Calculating overtime

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

✓ answered by NeoPa

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 7482
NeoPa
32,556 Expert Mod 16PB
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
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
By the way I am using Access 2010 ;)

I just read your "How to ask good Questions" Thread.
Nov 3 '11 #4
NeoPa
32,556 Expert Mod 16PB
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.
  1. 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.
  2. 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.
  3. 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
  1. SELECT   [EmployeeNo]
  2.        , [Enter Week Start] AS [WeekStart]
  3.        , Sum(HoursAndMinutes([ClockInDate], [TimeIn], [ClockOutDate], [TimeOut])) AS [TotalHours]
  4.        , IIf([TotalHours] > 40,[TotalHours] - 40,0) AS [OvertimeHours]
  5.        , [TotalHours] - [OvertimeHours] AS [StandardHours]
  6. FROM     [tblTimes]
  7. WHERE    ([TimeType] = 'N')
  8.   AND    ([ClockInDate] Between [Enter Week Start] And DateAdd('d', 6, [Enter Week Start]))
  9. GROUP BY [EmployeeNo]
Nov 11 '11 #5
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
  1. 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
NeoPa
32,556 Expert Mod 16PB
  1. New thread. Good idea. Definitely what you need to do for any new questions.
  2. 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.
  3. I'm pleased to hear that. It shows good understanding.
  4. 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
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
NeoPa
32,556 Expert Mod 16PB
That's certainly more compicated, but the following should be a good point to start from :

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([ClockInDate], [TimeIn], [ClockOutDate], [TimeOut])) 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    ([ClockInDate] Between [Enter Start Date] And [Enter End Date])
  10. GROUP BY [EmployeeNo]
  11.        , DateDiff('d',[Enter Start Date],[Enter End Date]) \ 7
Nov 23 '11 #9
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
File Type: jpg OTHoursQuery.jpg (62.0 KB, 2656 views)
Nov 29 '11 #10
NeoPa
32,556 Expert Mod 16PB
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.
Nov 30 '11 #11

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

Similar topics

2
by: Eric Whittaker | last post by:
here's my dilemma, on the program below, i am trying to calculate overtime pay at time and a half, but instead of only counting the hours after 40, it counts all hrs at that rate, how can i avoid...
5
by: Jamie Pittman via AccessMonster.com | last post by:
I have two tables with 5000 entries on them. One is based for regular time with several variables example (employee name, date,time in and out, code, customer, building) I have another table that...
3
by: michb | last post by:
I need to be able to calculate on a daily basis, both normal and overtime hours for both payroll and job costing. I also then need to calculate the above on a weekly basis, in order to complete...
1
by: Erick | last post by:
I have two fields in a table to record time and Time. I'm using the Date Diff function to calculate the difference. I'm trying to write an expression that will calculate any time over 8. I"m...
1
by: hakunamatata5254 | last post by:
Hi all, In my project of multiple forms (main form, subform1, 2 , 3 etc) (Main form is Employees, Subforms are Attendance, salary details, payment) Now i want to calculate the payment based...
3
by: foxykitty | last post by:
Hi, How can I group in my qryMonthlyHours in SQL view by , and then sum done in that month? I have another query - qrySaturdayRota ,,,,, ALL THIS IS GROUPED EXCEPT FOR has a criteria...
6
by: Rickster66 | last post by:
I have a database where order numbers are entered by a group of 25 users. The Access database is split. As each order number is entered, a time stamp using a general format is used on the table. Now,...
1
cori25
by: cori25 | last post by:
Employee's input the shifts they want, once I have all this data I need to determine who will get what shifts depending on a reliability %, if thats the same then I look at the time stamp. I have...
1
by: Brendan Masters | last post by:
We are looking to capture overtime and would like to know what is the best program to use. There are approx. 50 people working for use and we need them to add their overtime each day to a database....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.