Solved

Create Weekly Query Summing Hours And Overtime

Posted on 2011-02-11
19
811 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:Harper-IT
  • 9
  • 8
  • 2
19 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 34872730
What is the structure of the table that stores the hours worked?
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 34874517
Listed below is the documentation to the table:

 Table Structure
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?
0
 
LVL 7

Expert Comment

by:tlovie
ID: 34874709
So is it correct to assume then, that for a given week, for example 1, there would be 5 records.
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 34874819
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!  :)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34899569
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, ...post a database that we can easily open and immediately see the issue.


JeffCoachman

0
 
LVL 1

Author Comment

by:Harper-IT
ID: 34901568
I typed up what I am trying to accomplish with this database in Microsoft Word.
 Trying-To-Create-a-Time-Clock-Da.doc
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34917976
I would still need a sample of the actual database...
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 34930422
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!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34957752
ok
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:Harper-IT
ID: 34991339
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.
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 35001931
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!
010411.txt
tblDepartmentTimes.txt
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35003728
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...


JeffCoachman
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 35008304
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35009640
Then post a sample of the Actual DB file you are working with...
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 35022097
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?
TimeClock.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35090822
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...
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 35091089
FWIW, things like this are typically (and more easily) done in a "Report"

See the attached sample
Access-EEQ26815133reportOverTime.mdb
0
 
LVL 1

Author Comment

by:Harper-IT
ID: 35095374
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!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35097100
Well, then I am glad I could help.

;-)

Jeff
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now