Link to home
Start Free TrialLog in
Avatar of Harper-IT
Harper-ITFlag for United States of America

asked on

Create Weekly Query Summing Hours And Overtime

One of the pains of getting new software is losing some features that you don't want to lose.  I am trying to create a query that takes an employee's time (from Sunday to Saturday) and do a sum.

The trick is, trying to calculate the overtime - and I will explain why.  I have a table that has the employee's number, the date they clocked in and the time they clocked in and out.  I used the DATEDIFF and am able to calculate the hours worked for the day.  Overtime is not calculated until they have worked 40 hours that week.  So if a person worked the following hours during the week:  8, 8, 9, 7, 8 - That is 40 hours.  The 9 hour day is not considered 8 hours with 1 hour of overtime.

Now if a person worked the hours:  10, 10, 10, 10, 8 (48 hours for the week), that would mean on the end of day 4, they worked 40 hours.  Day 5 would then be put in the "Overtime" field and not the "Standard Hours" field.

And to make things even more complicated, they need to show the time in decimal format.  So if someone worked 4 hours and 30 minutes of overtime it wouldn't show as 4:30 but rather 4.5.

I have been working on this for the past week and can't seem to find a solution.  Any help or suggestions is greatly appreciated!
Avatar of tlovie

What is the structure of the table that stores the hours worked?
Avatar of Harper-IT


Listed below is the documentation to the table:

 User generated image
The first 7 fields are what is brought in from a flat file.  Fields 8-11 (PostINTime - Dept) are created from a query that looks at the employee's department and puts the hours they are supposed to work.

So, if an employee works 8-5, then that is what goes into the "Post" times.  When they actually clock in and out goes to INTime and OUTTime.

The fields that begin with "n" are from the flat file that is imported.  I don't use them in any queries, but I do need them to create the new flat file that will go into the new accounting software.

Does this make sense?
So is it correct to assume then, that for a given week, for example 1, there would be 5 records.
LOL...Not necessarily.  If you are in departments 125, 126, 127 and 128 then you clock in in the morning, clock out for lunch, clock in from lunch and clock out for the day.  If you are in any other department, you clock in in the morning and clock out for the day.

This is why I am having so much fun with this...NOT!  :)
Avatar of Jeffrey Coachman
Then post a sample of this Database.
Along with this please post a graphical example of the *Exact* output you are expecting based on the sample data.

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Delete any objects that do not relate directly to the issue.
5. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
6. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
7. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
8. Compile the code. (From the database window, click: Debug-->Compile)
9. Run the compact/Repair utility.
10. Remove any Passwords and/or security.
11. Post explicit steps to replicate the issue.
12. Test the database before posting.

In other words, a database that we can easily open and immediately see the issue.


I typed up what I am trying to accomplish with this database in Microsoft Word.
I would still need a sample of the actual database...
Sorry for the slow response.  I am in the process of implementing 2 Windows 7 computers into an XP world.  I will get you a sample as soon as I get time.  Thank you for your patience!
I haven't forgotten about you.  I have to edit some files to hide the names of individuals for security reasons.  Once I get this done, I will post the files.
Maybe this will make it easier.  I have attached the file that the time clock generates.  The file tblDepartmentTimes.txt has the following fields:  Department, DepartmentIndex, ShiftStart, ShiftEnd.  The field DepartmentIndex is the same value as the 9th column in 010411.txt (the time clock's version of the department).

The goal is to import the 010411.txt file into Access 2003.  Once the imported data is in a table, add the fields PostInTime, PostOutTime, PostHours, and PostOT.  The new Post fields would then be populated with the start and stop times for their department.  When the IN and OUT times are populated (or changed manually) the PostHours calculates how many hours and minutes you have worked (with OT being calulated after they work 40 hours that week).

Once this is all done, it will append it to the other times that have been imported so that we can run reports.  One of those reports is to recreate the text file that was imported, but substituting the IN, OUT, HOURS and OT with the POSTIN, POSTOUT, POSTHOURS and POSTOT.  Keep in mind that the POSTHOURS worked will be in decimal format (example 6:35 or 6 hours and 35 minutes = 00635).

This text file will then be imported into our new accounting software.  I appreciate everyone looking at this!
Then what is the one direct goal of this question?
Import the file?
Calculate Post Hours?
Append Times?
Create Report?
All of the above?
None of the above?

This is beginning to read more like a request for a "Complete project" than a single question with one straightforward answer...

The main question is this:  How do I calculate overtime hours for the week (Sunday - Saturday)?  Overtime is considered anything over 40 hours during the week.

The other stuff you can disregard.  I put that out there to bounce off of other people's heads to see if I was thinking in the right direction.

I am sorry for any confusion this may have caused.
Then post a sample of the Actual DB file you are working with...
Here is a copy of my database.  I first import the text file into tblImportedTime.  I then run the query qryImportTimeMod1 on tblImportTime to change the dates to have "/" in them and times to have ":" in them.  I also change the hours and overtime fields to a decimal format (000.00).  This query also looks at the department number and puts in the time their shift starts and ends.  All this is appended to the table tblImportedTimeMod1.

I then run query qryImportTimeMod2 on tblImportTimeMod1.  This query looks at your department and calculates the hours worked.  Departments 125, 126, 127 and 128 do not have 30 minutes taken off for lunch.  The rest of the departments do.  This data is then appended to tblImprtTimeMod2.

This is where I get stuck.  How do I calculate overtime (which is considered anything over 40 hours for the week)?  If a person worked the following hours:  10, 10, 10, 8, 8 (Mon - Fri).  Then Mon-Thurs they would have 38 hours posted under PostedHours and 0 posted under PostOT1.  On Friday, they worked an extra 8 hours.  This would mean that 2 of those 8 hours would be posted under PostedHours and 6 hours would be posted under PostOT1.  Does this make sense?
So tblImprtTimeMod2 is the final table?

If so, then most EmpId's only have 1 record (Tuesday 1/4/2011) , so I can't see how you would calculate Overtime if only one day is in the table?

Or am I missing something?

In other words, show me an employee in the table with over 40 hrs and show me *Exactly* how you want their regular hours and OT depicted in the table...
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry about only having one day in the database.  I cleaned up the database and forgot to import one weeks worth of time clock data.  The report you attached is what I am looking for!

This is why I brought my question here.  I thought I was going down the right path in my thinking, but wanted another person's view.  Obviously, my thinking was headed the right way...just taking the long route!

Thank you for your help!  This works for me!!!
Well, then I am glad I could help.