Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Question Unanswered: Time Clock Calculation Question

    I'm developing a time clock using Access for my own company use, but I'm having some trouble calculating the amount of hours an employee worked for the day.

    I read a previous post, and the post's suggestion works great, but there is one problem.

    My table records punches, 1 time field for In/Out time, 1 time field for Lunch In/Out.

    The following calculation works great in this senario.

    (Max([InOutTime])-(Min([InOutTime])) - (Max([LunchInOutTime])-Min([LunchInOutTime]))

    The only problem with this is if the employee clocks in again on the same day to work extra, this calculation no longer works.

    Any suggestions??

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Time Clock Calculation Question

    So there can be a maximum of 3 punch-ins punch-outs? Or can there be an unlimited number of punch-ins punch-outs?

    If it is a maximum of three then add the extra to your formula

    (Max([InOutTime])-(Min([InOutTime])) - (Max([LunchInOutTime])-Min([LunchInOutTime]) - (Max([OverTimeInTime])-Min([OvertimeOutTime]))

    Otherwise I would suggest you have 2 variables only In and Out then loop through your recordset adding them up.
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    I'm sure it will rarely go more than 3 punches, but it would be nice to have the unlimited option. Right now actually I have the 2 variables in and out, so how would you make it loop and add?

    Abdiel

  4. #4
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    You will need two loops, one that loops through each individual and a second that loops through each punch-in punch-out combination. Here is some sample code, I will write it quickly so you will have to check the syntax and correct any SQL queries to match your database, but this is how to do it:

    Do While Not rsIndividuals.Eof

    rsPunchInPunchOut.Open "SELECT PunchInPunchOut From Table WHERE Name ='" & rsIndividual.fields("Name").Value & "'"

    Do While No rsPunchInPunchOut.Eof

    TotalTime = Max([InOutTime])-(Min([InOutTime]))
    rsPunchInPunchOut.MoveNext

    Loop

    MsgBox TotalTime & "TotalTime for " & rsIndividual.fields("Name")
    rsIndividual.MoveNext

    Loop
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    doesn't this loop assume that the in-out punch in on the same record? ie 1 record, 2 fields. 1 for in 1 for out

    Currently I have 1 record per punch. 1 field that records the time, and 1 field that indicates a 1 if it is a IN punch, 2 if it is an OUT punch



    EDIT>> I see where you are going with this... I'll just have to modify my table, and make it so that when they punch out, it updates out field in the last record they had an IN punch for right?
    Last edited by xtechdata; 04-01-04 at 13:42.

  6. #6
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Yes I was assuming both punch-in and punch-out combinations were in one record.

    With your current 1 record/punch how do associate which punch-in goes with which punch-out?
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •