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

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.