Calculating Hours clocked 3

[JOEYB99]

I am using Access 2007 and have started development of a database of time and attendance transactions.

The records look like this; Date, Time, ClockLocation, EmployeeName, EmployeeNumber, In/Out

Employees are clocking in or out, and they can do so more than once in a day. So I could have multiple records for one employee on the same date! My challenge is figuring out their total hours clocked for a given day.

Can someone please help me with an approach/solution to achieve this? I am not familiar with VBA code. However, I can get help to implement it (code) if someone provides me with a solution/attempt using VBA code.

I'm assuming I will have to sort the data by employee, then by time within date. Then I will have to check that the "outs" always follow the "ins".

Any help would be greatly appreciated, even a web-site that references an approach.

 

[Andrzejek]

First I would probably eliminate first 2 fields (Date and Time) where you would probably keep the information of when the record was created.

Then I would split In/Out into 2 separate fields: In and Out (defined as Date/Time).
Emploees first have to check In, then they check Out, right? So every record would be their attendance. It is easy to calculate the time difference between the 2 fields since In will always be before Out, right? And it even does not matter if they check In at 11:00 PM and go home at 4:00 AM next day

Have fun.

---- Andy

 

[dhookom]

Date and Time are both reserved words and should not be used to name anything. I would probably leave the structure similar to what you have although the employee name is unnecessary if you have the EmployeeNumber.

Have you checked any data to make sure the employees always clock in and out? Does this need to be part of your solution? What do you expect to happen if employees forget?

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

The comments from Andy and Duane are on the money. The fundamental issue that you will need to deal with is missing or incorrectly entered data. People do forget, or for whatever reason are prevented from, faithfully clocking in and clocking out. If your user interface allows a user to choose "I'm clocking in" or "I'm clocking out" thus setting your In/Out field then you need to deal with successive "In" entries with no "Out" between them or conversely, two "Outs" with no intervening "In". That is actually a much bigger problem than just computing elapsed times.

A few other potential issues.

- Is this system intended to form the basis for people's pay? If it is then you need a full audit trail. People get real touchy about their paychecks.

- Do you have people starting work on one day and finishing the next? If so then you will need to handle allocating the time worked to different days. That may be important because such a day roll over on the last day of a month could mean that some of the time appears on one paycheck and some on the next. Similar comments apply to working holidays if different rates apply than those for a regular work day.

- Do you pay overtime? In some jurisdictions overtime is a legal requirement beyond a certain number of hours per day. Do you allow an employee who is supposed to start work at 9:00 AM to clock in at 6:00 AM, thus awarding himself three hours of overtime?

- Some companies compute pay in (for example) 15 minute increments. That means that, before computing time worked, you need to round the raw clock times to the appropriate 15 minute break points before computing the time worked.

- Is this system tied to a scheduling function? If so, is an employee allowed to clock in when they are not scheduled to work? If they cannot, is there some management over-ride so that the boss can call Joe on his day off to come in and fix some problem?

Given all that, here's some code that assumes:
- Employees never fail to clock-in and clock-out.
- In/Out pairs always occur on the same day.
- Elapsed time is based on the raw times.
- There are no restrictions on when an employee can clock in or out.

Public Sub ComputeTimes(FromDate As Date, ToDate As Date)

Dim db               As DAO.Database
Dim re               As DAO.Recordset    ' List of employees
Dim rt               As DAO.Recordset    ' Clock Times for an Employee
Dim SQL              As String
Dim n                As Integer
Dim ElapsedTime      As Long
Dim ThisDate         As Date
Dim InTime           As Date
Dim OutTime          As Date

On Error GoTo UnPleasentness

Set db = CurrentDb

' Make sure that the input parameters do not have a time value
FromDate = CDate(Int(FromDate))
ToDate = CDate(Int(ToDate))

' Get a list of employees that have at least one time
' between the specified dates.
Set re = db.OpenRecordset( _
         "SELECT DISTINCT EmployeeNum From TheTable " & _
         "WHERE CDate(Int(ClockDT)) Between #" & FromDate & "# AND #" & ToDate & "#;", _
         dbOpenSnapshot)

' Start the loop to process the employees
Do Until re.EOF

    ' Process each date in the range FromDate ---> ToDate
    ThisDate = FromDate
    Do Until ThisDate > ToDate
        SQL = "SELECT ClockDT FROM TheTable " & _
              "WHERE EmployeeNum = " & re![EmployeeNum] & _
              "  AND CDate(Int(ClockDT)) = #" & ThisDate & "# " & _
              "ORDER BY ClockDT "

        Set rt = db.OpenRecordset(SQL, dbOpenDynaset)

        If rt.EOF And rt.BOF Then
            ' The employee has no records on ThisDate
        Else
            n = 0
            ElapsedTime = 0
            Do Until rt.EOF
                n = n + 1
                If n Mod 2 = 1 Then
                    ' Odd Number ---> Clock In
                    InTime = rt![ClockDT]
                Else
                    ' Even Number ---> Clock Out
                    OutTime = rt![ClockDT]
                    ElapsedTime = ElapsedTime + DateDiff("n", InTime, OutTime)
                End If
                rt.MoveNext
            Loop

            If n Mod 2 = 1 Then
                ' Raise an error ... We found an Odd-Number of values
            Else
                ' Done with that employee on that Date. Save the results
                SQL = "INSERT INTO tblTimes (EmployeeNum, WorkDate, WorkMinutes) " & _
                      "VALUES (" & re![EmployeeNum] & ", #" & ThisDate & _
                      "#, " & ElapsedTime & ")"
                db.Execute SQL, dbFailOnError
            End If
        End If
        
        ' On To the next Day
        ThisDate = ThisDate + 1
    Loop

    ' And do another employee
    re.MoveNext
Loop

Normal_Exit:
Set re = Nothing
Set rt = Nothing
Set db = Nothing
Exit Sub

UnPleasentness:
MsgBox Err.Number & " - " & Err.Description
Resume Normal_Exit

End Sub

 

[JOEYB99]

Thanks Golom, and Duane and Andy.

Golom, your VBA code looks like it should work based on your assumptions. However, where exactly do I insert this VBA code?

 

[Golom]

Insert it in a module. Exactly how you want to use it depends on how your application is structured. Note that you will need to create the table "tblTimes" before you use it. That is where the Sub saves its results.