I have created Three tables (1) tblEmployee (2) TblAttendenceDate (3) tblAttendenceMain following fields
(1) tblEmployee
a. pkEmployeeID Autonumber
b. FirstName (text)
c. BasicSalary (Currency)
(2) tblAttendenceDate
a. pkAttendID Autonumber
b. Attendencedate Date
(3) tblAttendenceMain
a. pkID Autonumber
b. fkAttendID Number
c. fkEmployeeID Number
d. AttendStatus text (lookup – value list “Present”;”OnLeave”;”Absent”
e. ReportingTime Text
f. ReportedAt Text
With the help of Table No.2 and 3 I have created a Form as frmAttendence which includes a SubFormAttendence
The SubFormAttendence records the attendance of the employees based on the following function
Private Sub Form_AfterUpdate()
If IsNull(Me.ReportTime) Then
Me.ReportTime.Value = Format(Date, "dd/mmm/yyyy") & " 9:50:00 AM"
End If
If IsNull(Me.ReportedAt) Then
Me.ReportedAt.Value = Now()
End If
End Sub
The Form frmAttendence has a control with control source as “AttendenceDate” which records the date and the subformAttendence records the AttendStatus when the employee attends the office and marks attendence.
The reporting time of the employee is 9.50 AM.
If AttendStatus is “Present” and employee attends the office well in time will mark the attendance and there is no problem. But any employee who attends the office between 9.51 and 9.55 he is marked a “lateMark” noted as LM. And any employee who marks attendance after 9.55 he is treated as on “Shortleave” noted as SL.
The system has to count the LateMarks (LM) and ShortLeave (SL) and Leave/absent days.
The Problem: The Department will release the monthly salary to the employee subject following conditions
1. As a goodwill gesture, Any Employee who never comes late nor remains on leave for the whole month, will get 2 days salary as Bonus (on prorate basis), call it as “BonusSalary” besides his normal salary at the end of the month. (i.e. Date of Salary Disbursement).
2. He will not be penalized for 5 latemarks in a month.
3. If lateMarks are between 6 and 11, he will lose half days bonus. i.e he will be entitled to 1.5 days bonus salary only. Between 12 and 17 Latemarks, he will lose 1 day “bonusSalary”. Between 18 to 24 he will lose 1.5 days “bonusSalary” and above 24 LateMarks, he will not get any Bonus Salary.
4. Likewise, for one “Shortleave” SL, the employee will lose .25 days “bonusSalary”. For two shortleaves he will lose .5 days “bonusSalary” and so on.
5. Similarly for one Casual Leave (CL) or one day absent he will lose 1 days “bonusSalary” and so on.
The “BonusSalary” may go negative if SL or Casual leave (CL) exceeds 2 days and the employee will lose some portion from his normal salary.
The problem is how to deduct the salary on the above conditions.
I could get the result for condition no 1 to 3 though the query. But the 4 and 5 could not be achieved.
Please help.
Thanks
Jalal