You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q237958
This article has been archived. It is offered "as is" and will no longer be updated.
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

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

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.
inf
Properties

Article ID: 237958 - Last Review: 12/05/2015 15:18:46 - Revision: 2.0

  • Microsoft Access 2000 Standard Edition
  • kbnosurvey kbarchive kbhowto KB237958
Feedback