Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009

    Unanswered: Creating a Time Clock


    I have created a database for our department to collect all of our usage statistics. I have been asked to integrate a time clock into the database whereby a user would select their username and either click “Clock IN” or “Clock OUT” and the system records their time.

    Here are the tables that I have setup that relate to this functionality:


    So far the system works great, but I haven’t figured out a way to compile and report the stats. I don’t need to worry about breaks but I do have to have the final time round in six minute increments.

    If you have suggestions for a better table setup to make reporting easier or any help would be greatly appreciated and I would be happy to clarify or answer any questions.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    I'm not sure to understand your question. What do you have so far (apart from a table definition) and what problems did you encounter?

    What do you mean by "compile and report the stats"?

    Sorry but it's almost impossible to help you with the information you provided.

    Have a nice day!

  3. #3
    Join Date
    Aug 2009

    Re: Sinndho


    Thanks for replying. I'm having problems when I try to develop reports from the clocking IN/OUT records. What I would like is a report for each individual that allows me to select a series of dates, the user, and for each day an individual worked over the course of these series of dates it would tell me how many hours that employee worked each day.

    What I also need it to do is to round for every six minutes. So if someone clocks in at 2:00 PM and clocks out at 6:05 PM, it records the time worked as 4 hrs. But if they clock in at 2:00 PM and clock out at 6:07 PM it records the time worked as 4.1 hrs.

    I have attached a copy of the paper time card that we now have the student employees fill out. Hopefully, that will help to clarify what I need.

    If it helps I have also provided a copy of my VB script that I use for that form.

    Does this help?
    Attached Files Attached Files

  4. #4
    Join Date
    Mar 2009
    Provided Answers: 14
    Yes, it's clearer now, thanks.

    It could probably be done with one big query with several subqueries, but such a query would be difficult to understand, to manage and to debug (not to mention hard to write). Moreover I'm no big fan of complex queries and I'm no SQL geek either (no offence intended).

    Here is a solution (what I provide is an example but you should not have much trouble to adapt it) The tables are not completely normalized and there are probably more efficient solutions, but this one works:

    Table Tbl_Employees:
    SysCounter, AutoNumber, Primary Key
    First_Name, Text 50
    Last_Name, Text 50

    Table Tbl_Clockings:
    SysCounter, Autonumber, Primary key
    FK_Employees, Number Long, Not Null, Indexed Duplicates OK
    Start_Time, Date/Time
    End_Time, Date/Time

    Table Tbl_Clocking_Output:
    SysCounter, AutoNumber, Primary key
    Employee_Id, Number Long, Not Null, Indexed Duplicates OK
    First_Name, Text 50
    Last_Name, Text 50
    Day_Of_Week, Text 2
    Date_Of_Month, Text 5
    In_1, Date/Time
    Out_1, Date/Time
    In_2, Date/Time
    Out_2, Date/Time
    In_3, Date/Time
    Out_3, Date/Time
    Daily_Hours, Number Byte
    Daily_Minutes, Number Byte
    Running_Total, Number Integer

    We shall use a function to fill the Tbl_Clocking_Output table. This function accepts 3 optional parameters: Employee ID, Month and Year.
    - If Employee ID is not supplied, the fuction will process all employees.
    - If both Month and Year are supplied, the function will work on the supplied month of the supplied year.
    - If Month is supplied but Year is not, the function will work on the current year.
    - If Year is supplied but Month is not, the function will work on the supplied year.
    - If neither Month not Year are supplied, the function will work on the whole Tbl_Clockings table (with the possible Employee restriction).

    It should not be difficult to create a report based on the Tbl_Clocking_Output table.

    I could not solve the rounding problem because I did not have enough information about the rules of rounding, but once more it should not be difficult to adapt the function in order to do so.

    Here is the function:
    Function Fill_Tbl_Clocking_Output(Optional ByVal lngEmployeeId As Long, Optional ByVal intMonth As Integer, Optional ByVal intYear As Integer)
        Dim rstEmployees As DAO.Recordset
        Dim rstClocking As DAO.Recordset
        Dim rstClockingOutput As DAO.Recordset
        Dim strSQL As String
        Dim strWhere As String
        Dim lngCurrentAbsoluteDay As Long
        Dim lngCurrentEmployeeId As Long
        Dim lngTotalMinutes As Long
        Dim intRowCount As Integer
        Dim strPeriodStart As String
        Dim strPeriodEnd As String
        ' Open Tbl_Employees as a tableset so we can use .Seek which is a lot faster than .FindFirst.
        ' Cannot work with an attached table, though. Must open a recordset and use .FindFirst in that case:
        ' Set rstEmployees = CurrentDb.OpenRecordset("Tbl_Employees", dbOpenSnapshot, dbSeeChanges)
        Set rstEmployees = CurrentDb.OpenRecordset("Tbl_Employees", dbOpenTable, dbSeeChanges)
        rstEmployees.Index = "PrimaryKey"
        ' Prepare the query on the table Tbl_Clockings.
        strSQL = "SELECT FK_Employees, Start_Time, End_Time " & _
            "FROM Tbl_Clockings " & _
            "{Where} " & _
            "ORDER BY Tbl_Clockings.FK_Employees, Tbl_Clockings.Start_Time;"
        ' Prepare the WHERE clause of the query, according to the received arguments.
        ' 1. For the period:
        If (intMonth = 0) And (intYear = 0) Then
            strWhere = ""
            If intYear = 0 Then intYear = DatePart("yyyy", Now)
            If intMonth = 0 Then
                strWhere = " WHERE (Start_Time Between #1/1/" & intYear & "# AND #31/12/" & intYear & "#)"
                ' Date format in Europe (and probably in other places).
                If CDate(36891) = "31/12/2000" Then
                    strPeriodStart = "1/" & intMonth & "/" & intYear
                    strPeriodStart = intMonth & "/1/" & intYear
                End If
                ' Compute first day of next month as end date.
                strPeriodEnd = "#" & Format(DateAdd("m", 1, strPeriodStart), "mm/dd/yyyy") & "#"
                strPeriodStart = "#" & Format(strPeriodStart, "mm/dd/yyyy") & "#"
                ' WHERE clause for the period completed
                strWhere = " WHERE (Start_Time Between " & strPeriodStart & " AND " & strPeriodEnd & ")"
            End If
        End If
        ' 2. For the employee.
        If lngEmployeeId <> 0 Then
            If Len(strWhere) Then
                strWhere = strWhere & " AND "
                strWhere = " WHERE "
            End If
          ' WHERE clause for the employee completed.
           strWhere = strWhere & "(FK_Employees = " & lngEmployeeId & ")"
        End If
        ' Complete the query with the WHERE clause.
        strSQL = Replace(strSQL, "{Where}", strWhere)
        Set rstClocking = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
        ' Purge Tbl_Clocking_Output and open it (Dynaset: we'll write in it).
        strSQL = "DELETE * FROM Tbl_Clocking_Output"
        CurrentDb.Execute strSQL, dbSeeChanges
        Set rstClockingOutput = CurrentDb.OpenRecordset("Tbl_Clocking_Output", dbOpenDynaset, dbSeeChanges)
        ' Loop through the query
        With rstClockingOutput
            Do Until rstClocking.EOF
                ' Find employee infos
                ' .Seek does not work if the recordset was not open as dbOpenTable, must use:
                ' rstEmployees.FindFirst "SysCounter = " & rstClocking!FK_Employees
                rstEmployees.Seek "=", rstClocking!FK_Employees
                If rstEmployees.NoMatch Then
                    ' Error: cannot be a row in Tbl_Clocking with no matching row in Tbl_Employees.
                    Stop    ' Should be replaced by a proper error handler.
                End If
                ' Used for detecting a change in date or employee Id.
                lngCurrentAbsoluteDay = Int(rstClocking!Start_Time)
                lngCurrentEmployeeId = rstClocking!FK_Employees
                ' Fill the output table
                ' Administrative infos
                !Employee_Id = rstEmployees!SysCounter
                !First_Name = rstEmployees!First_Name
                !Last_Name = rstEmployees!Last_Name
                !Day_Of_Week = UCase(Left(Format(rstClocking!Start_Time, "ddd"), 2))
                !Date_Of_Month = Format(rstClocking!Start_Time, "mm/dd")
                ' Initialize counters
                lngTotalMinutes = 0
                intRowCount = 0
                ' Clocking infos (from 1 to 3 rows in the query).
                ' No need to use a recursive function as the number of In's and Out's is fixed.
                Do Until (Int(rstClocking!Start_Time) <> lngCurrentAbsoluteDay) Or (rstClocking!FK_Employees <> lngCurrentEmployeeId)
                    intRowCount = intRowCount + 1
                    lngTotalMinutes = lngTotalMinutes + DateDiff("n", rstClocking!Start_Time, rstClocking!End_Time)
                    Select Case intRowCount
                        Case 1
                            !In_1 = rstClocking!Start_Time
                            !Out_1 = rstClocking!End_Time
                        Case 2
                            !In_2 = rstClocking!Start_Time
                            !Out_2 = rstClocking!End_Time
                        Case 3
                            !In_3 = rstClocking!Start_Time
                            !Out_3 = rstClocking!End_Time
                        Case Else
                            ' Error: cannot be more than 3 clockings in one day.
                            Stop    ' Should be replaced by a proper error handler.
                    End Select
                    ' End of query reached?
                    If rstClocking.EOF Then Exit Do
                ' Store totals for the day
                !Daily_Hours = lngTotalMinutes \ 60
                !Daily_Minutes = lngTotalMinutes Mod 60
                !Running_Total = lngTotalMinutes
            ' Clean up
            Set rstClocking = Nothing
        End With
        Set rstClockingOutput = Nothing
        Set rstEmployees = Nothing
    End Function
    You'll find a sample mdb in attachment.

    Have a nice day!

  5. #5
    Join Date
    Mar 2009
    Provided Answers: 14
    Oops! I forgot to attached the file, sorry!

    Have a nice day!
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2007
    Adelaide, South Australia
    I think you'll get a distinction for that
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Aug 2009

    Smile Re: Sinndho

    Thank you very much! This was above and beyond what I was hoping for for help! I see the tweeking that it will take to apply to my situation and will work on that over the weekend.

    Quote Originally Posted by Sinndho
    Oops! I forgot to attached the file, sorry!

    Have a nice day!

Posting Permissions

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