Results 1 to 9 of 9
  1. #1
    John V is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    43

    Timesheet Database (Compare one record to another)

    I am playing with a new project this week and I am trying to formulate in my head how I would go about doing this.



    I want to create a database for tracking employees hours of work. I would like to have a form where I choose an employee's name from a combobox and select a date, then have a subform (in datasheet view) where I enter the employee's time record and have it calculate the hours based on a set of rules. This seems like quite a simple thing but I have a feeling it is more complicated than I might suspect. Here is a picture of a timesheet to show you what I am trying to accomplish.

    Click image for larger version. 

Name:	Time Sheet.png 
Views:	26 
Size:	5.4 KB 
ID:	17975

    The first column (Number (PK)) would not actually show in the in the subform and the Hours X1, Hours X1.5, and Hours X2 columns would auto-calculate based on the clock records entered in the Start Time and End Time fields.

    As you can see the hours calculated for the first clock records entered actually include the hours from 11:00 to 14:15, not 11:00 to 13:45. This is because the time between the End Time of the 1st record and the Start Time of the 2nd record is only 0.5 hours and is therefore paid time. However, the time between the End time of the 3rd record and the Start Time of the 4th record is 1 hour and this is therefore unpaid time.

    I could obviously tell the database how long the breaks are and then have it calculate accordingly. However all of the information required to calculate the break (and make decisions appropriately) is available to the database if I can figure out how to get it to compare one record to another.

    This is the question I have. Is there a way (with VBA code) to compare the End Time field of one record with The Start Time field of another (next) record and have it calculate the time difference (number of hours, i.e. 0.5 hours, 1 hour, or more). Then using this information I can make decisions about how the total hours are calculated (is the break paid or not)?

    I hope this makes sense.

    TIA for any help.
    John
    Attached Thumbnails Attached Thumbnails Timesheet.png  

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Will this be a complete total over all records or just a select few? Are you actually referring to break time as in On Break or the difference between the two?

  3. #3
    John V is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    43
    I am not quite sure what you mean by "difference between the two"

    I simply want to make decisions about whether the employee is paid for their break or not. An employee who only gets a 0.5 hour break for lunch (or any meal) is paid for their meal break and employees who get a 1 hour meal break (or more) are not paid for this time.

    John

  4. #4
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    I don't know how your db is setup. I would have a separate breakin breakout field(s) myself. But it can be done if you know how many hrs they are supposed to get.
    You did not answer my question if this a total overall or calculated for just a few records which you seemed to indicate.
    If the employees time (assuming a 8 hr shift) is over by 1/2 hr then they don get paid but just for 8 1/2 hrs
    This would give you the difference between 2 dates as an example:

    DateDiff("n",[StartTime],[EndTime])/60
    In order to best guide you more details would be helpful. Those pic examples, are they calculated or manually added up?

  5. #5
    John V is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    43
    Burrina,

    I am not trying to be difficult in not answering your question. I was a little unsure, but now I think I understand.

    Careful inspection of the two datasheet images I included with my original post will show that the days involved are over 8 hours, in fact one of them is even over 12 hours in the day. So simply basing things on an 8 hour (or 12 hour) day will not give me the results I need.

    I work in an industry where we regularly work more than 8 hours and often more than 12 hours in the day, and most of it is unionised work that has negotiated rules regarding how the hours are calculated.

    For the purposes of the agreement this database will relate to the rules are this.

    Meal breaks are taken in half hour increments. If you are allowed a one hour meal break (or more) the break is not paid. If you are allowed a half hour meal break, the break is paid. This is the part of the agreement I am trying to get the database to figure out on its own (by using some VBA code).

    The other rules regarding the calculation of hours state hours worked over eight hours are paid at 1.5X, and hours over twelve hours are paid at 2x. (this doesn't have much to do with this discussion and is something that is easy to work with, based on running totals).

    Where the two concepts for calculating hours intercept is whether a given breaks is paid (half hour) or not (one hour or more). If the employee only receives half hour breaks they get to eight hours or twelve hours more quickly. Once again not really part of this discussion.

    So, referring back to the datasheet images I originally posted, what I want to accomplish is compare the End Time of Record 1 to the Start Time of Record 2 and decide how long the break was. Then compare the End time of Record 2 to the Start Time of Record 3 and decide how long the break was, then compare the End Time of Record 3 to the Start time of Record 4 and decide how long the break was, etc. I would then make decisions about how many hours are done in each "shift" and calculate them accordingly.

    This would be done on a "by employee", "by day" basis. Each employee has a timesheet for each day they work.

    I have not actually started to put this database together, but when I do I envision a table with the Start and End Times that will have Primary Keys for each record (obviously) and fields for Employee (FK) and Date

    It would only compare End and Start Times as stated above where the Employee (FK) and Date are equal.

    I don't really want to tell the database how long the break is, I think I can have code to figure that out, although I will if I have to. I am not convinced I have to.

    I do not mind having a command button that I would be required to click to make it calculate the hours once the "Work Shift" Times for the day (and a given employee) are entered.

    John

  6. #6
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Okay, makes more sense. Although while our db is in design mode, hard to advise. The below would be my code to calculate what you describe.

    BreakTime: Sum(DateDiff("n",[StartTime],[EndTime])/60)
    HTH.

  7. #7
    John V is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    43
    Dave,

    Thanks for the response. Could you give me a brief explanation of how this code works?

    Could I get rid of the "/60" if I used "h" instead of "n" for the interval ?

    I am unclear how the code denotes it is "subtracting" the Start Time of Record 2 from the End Time of Record 1. Could you explain?

    Thank you

    John V

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,548
    Calculating data of sequential records is tricky. Review http://allenbrowne.com/subquery-01.html#AnotherRecord
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    John V is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    43
    Thank you June 7. I will study this and see what I can work out. I always seems to come up with almost insurmountable tasks for myself. Yikes!


    John V

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-22-2014, 10:30 AM
  2. BeforeUpdate compare two fields on same record
    By blacknblue in forum Programming
    Replies: 2
    Last Post: 10-23-2012, 06:42 PM
  3. compare record
    By Fabdav in forum Programming
    Replies: 1
    Last Post: 10-20-2012, 07:40 PM
  4. Replies: 3
    Last Post: 11-23-2011, 12:25 AM
  5. Resource/Timesheet Database Design
    By lynchoftawa in forum Database Design
    Replies: 0
    Last Post: 06-06-2009, 10:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums