ACC2000: How to Calculate Daily Hours Based on Clock In/Clock Out Times

Article translations Article translations
Article ID: 237958 - View products that this article applies to.
This article was previously published under Q237958
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

SUMMARY

This article shows you how to calculate daily hours worked, regardless of the number of times an employee clocks in or out.

MORE INFORMATION

Follow these steps to create a sample database that calculates the total hours an employee works on a specified day:
  1. Create a new database, and then add the following tables to this new database:
       Table: Employees
       ----------------------------
       Field Name: EmpID
       Data Type: AutoNumber
       Field Size: Long Integer
       Indexed: Yes (No Duplicates)
    
       Field Name: EmpName
       Data Type: Text
       Field Size: 50
       Indexed: No
    
       Table Properties: Employees
       -------------------------------
       PrimaryKey: EmpID
    
       Table: EmpDates
       ----------------------------
       Field Name: DateID
       Data Type: AutoNumber
       Field Size: Long Integer
       Indexed: Yes (No Duplicates)
    
       Field Name: MyDate
       Data Type: Date/Time
       Format: Short Date
       Indexed: No
    
       Field Name: EmpID
       Date Type: Number
       Field Size: Long Integer
       Indexed: No
    
       Table Properties: EmpDates
       -------------------------------
       PrimaryKey: DateID
    
    
       Table: EmpTimes
       ----------------------------
       Field Name: TimeID
       Data Type: AutoNumber
       Field Size: Long Integer
       Indexed: Yes (No Duplicates)
    
       Field Name: TimeIn
       Data Type: Date/Time
       Format: Medium Time
       Input Mask: 09:00\ >LL;0;_
       Indexed: No
    
       Field Name: TimeOut
       Date Type: Date/Time
       Format: Medium Time
       Input Mask: 09:00\ >LL;0;_
       Indexed: No
    
       Field Name: DateID
       Date Type: Number
       Field Size: Long Integer
       Indexed: No
    
       Table Properties: EmpTimes
       -------------------------------
       PrimaryKey: TimeID
    					
  2. Create the following two relationships

    1. Employees to EmpDates (One to Many) on EmpIDEmpDates to EmpTimes (One to Many) on DateID
    2. EmpDates to EmpTimes (One to Many) on DateID

    and then enable the following options for these relationships:
    • Enforce Referential Integrity
    • Cascade Update Related Fields
    • Cascade Delete Related Records

  3. Create a new query, add the following SQL statement as the source for the query, and then save the query as qryTimes:
    SELECT TimeID, TimeIn, TimeOut, DateID, DateDiff("n",[TimeIn],[TimeOut]) AS CalcTime FROM EmpTimes;
  4. Create three new forms that are based on the following information:
       Form: frmEmployees
       -----------------------
       Caption: Employees
       RecordSource: Employees
    
          Text Box
          --------------------
          Name: txtEmpID
          ControlSource: EmpID
          Enabled: No
    
          Text Box
          ----------------------
          Name: txtEmpName
          ControlSource: EmpName
    
       Form: frmDates
       ----------------------
       Caption: Dates
       RecordSource: EmpDates
    
          Text Box
          ---------------------
          Name: txtMyDate
          ControlSource: MyDate
    
          Text Box
          ---------------------
          Name: txtDateID
          ControlSource: DateID
          Visible: No
    
          Text Box
          -------------------
          Name: txtTotalHours
    
          NOTE: In the following sample expression, an underscore (_) at
          the end of a line is used as a line-continuation character.
          Remove the underscore from the end of the line when re-creating
          this expression. 
    
          ControlSource: =[Forms]![frmEmployees]![Dates Subform]._
                         [Form]![Times Subform].[Form]![txtTotTime]
    
    
       Form: frmTimes
       -----------------------
       Caption: Times
       RecordSource: qryTimes
       Default View: Datasheet
    
          Text Box
          ---------------------
          Name: txtTimeIn
          ControlSource: TimeIn
    
          Text Box
          ----------------------
          Name: txtTimeOut
          ControlSource: TimeOut
    
          Text Box
          -----------------------------------------------------------------
          Name: txtCalcTime
          ControlSource: =Int([CalcTime]/60) & ":" & Int([CalcTime] Mod 60)
    
          Text Box
          ------------------------------------------------------------------
          Name: txtTotTime
          ControlSource: =Int(Sum([CalcTime])/60) & ":" & Int(Sum([CalcTime]) Mod 60)
    					
  5. Open the frmDates form in Design view, and then drag the frmTimes form from the Database window to the Detail section of the the frmDates form.
  6. Set the Name property of the subform object to Times Subform, and then save and close the form.
  7. Open the frmEmployees form in Design view, and then drag the frmDates form from the Database window to the Detail Section the frmEmployees form.
  8. Set the Name property of the subform object to Dates Subform.

Testing the Example:

  1. Double-click the frmEmployees form.
  2. In the EmpName box, enter Joe User.
  3. In the MyDate box, enter 1/2/2001.
  4. In the TimeIn box, enter 09:00AM.
  5. In the TimeOut box, enter 12:00PM.
  6. Press TAB until the focus is in the TimeIn box on a new row, and then enter 01:00PM.
  7. In the TimeOut box, enter 06:00PM.
  8. On the Records menu, click Save Record. Note that the total time per row is displayed on the Times subform, and the sum of these rows is reflected on the Dates subform.

Properties

Article ID: 237958 - Last Review: June 29, 2004 - Revision: 2.0
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto KB237958

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com