Results 1 to 9 of 9
  1. #1
    jalals is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    24

    Bonus Salary on the basis of attendence of an employee

    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

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Firstly thankyou for providing all this info on your problem, it makes it much easier to come to a solution!.

    The only thing I have to ask is, do you really need to do this in a query, or could you output it on a form or a report?

  3. #3
    jalals is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    24
    Thanks Badger

    I need it in a query, then i can get it on form/report

    Thanks once again
    jalal

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Please post the SQL of your query so far. Won't have to re-invent what you have...... Thanks

    Also, where/how are LM recorded?

    What is a Casual Leave?

  5. #5
    jalals is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    24
    Quote Originally Posted by ssanfu View Post
    Please post the SQL of your query so far. Won't have to re-invent what you have...... Thanks

    Also, where/how are LM recorded?

    What is a Casual Leave?
    Attachment 7852


    I am sorry I could not mention (CL) earlier. However, I have corrected it now
    If you wish I will mail you database (zipped) which I have developed you will be able to understand me easily. However I give the details hereunder.

    With the help of Table No.2 and 3 I have created a Form as frmAttendDate which includes a SubFormAttendence

    The SubFormAttendDate 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 frmAttendDate 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 attendance by pressing “P” Key (P for Present) against his/her EmployeeID.


    The Sub form comprises following fields
    1. EmployeeID
    2. AttendStatus (It is a combo box with the valuelist as “Present”; ”OnLeave”;”Absent”
    ReporortTime (The reporting time of the employee is 9.50 AM

    3. ReportedAT (The system records both ReportTime and ReportedAT when the “P” key is pressed on account of function Private Sub Form_AfterUpdate() mentioned above.

    4. LateByMinutes (Time difference between ReportTime and ReportedAT in minutes)
    5. AttendReport (This is the expression field which I have derived as under:

    LateByMinutes:=IIf([AttendStatus]="Present",DateDiff("n",[ReportTime],[ReportedAt]),IIf([AttendStatus]="OnLeave",991,IIf([AttendStatus]="Absent",992,0)))

    {Here I have coded onleave =991 and Absent=992 otherwise there is a datatype mismatch in AttendReport field.}

    AttendReport:=IIf([LateByMinutes] Between 1 And 5,"LateMark",IIf([LateByMinutes] Between 5 And 60,"ShortLeave",IIf([LateByMinutes]=992,"Absent",IIf([LateByMinutes]=991,"OnLeave",0))))

    At the end on the month the system should count “LateMark”, “ShortLeave”; “Onleave” and “Absent” and settle the salary as per the following Rule.

    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 “LateMark” in a month.

    3. If “LateMark” 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 “Latemark”, he will lose 1 day “bonusSalary”. Between 18 to 24 he will lose 1.5 days “bonusSalary” and above 24 “LateMark”, he will not get any Bonus Salary.

    4. Likewise, for one “ShortLeave” the employee will lose .25 days “bonusSalary”. For two “Shortleave” he will lose .5 days “bonusSalary” and so on.

    5. Similarly for one “OnLeave” or one day “Absent” he will lose 1 days “bonusSalary” and so on.
    The “BonusSalary” may go negative if “ShortLeave” or “Onleave” 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

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can't open the attachment.
    If possible, please attach your mdb. Do a compile and repair, then zip it. Please ensure to remove or change any sensitive info.
    Thanks

  7. #7
    jalals is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    24
    Quote Originally Posted by ssanfu View Post
    Can't open the attachment.
    If possible, please attach your mdb. Do a compile and repair, then zip it. Please ensure to remove or change any sensitive info.
    Thanks
    Click image for larger version. 

Name:	Attendence.png 
Views:	12 
Size:	91.9 KB 
ID:	7880

    Hi

    How will i attach mdb. I can't attach it here. However the attachment is here

    Thanks

  8. #8
    jalals is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2012
    Posts
    24
    I think I could attached the mbd file

    Please check

    Thanks
    Jalal
    Attached Files Attached Files

  9. #9
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've looked at your db and steps 2 - 5 should be done in an end of month query, not the daily enter hours query. The monthly query should be a totals query, then a second query would do the calcs to determine how many hour/days to add or subtract from the monthly pay.

    I don't know if the posted example is the full or partial database, but IMO, there is still work that needs to be done on the structure. I just don't understand what your idea is as far as data entry and forms.

    I can't help with your problem - maybe someone else has some ideas..

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to calculate salary in parts
    By jalals in forum Access
    Replies: 6
    Last Post: 05-17-2012, 12:57 PM
  2. Replies: 1
    Last Post: 05-11-2012, 06:36 AM
  3. Access salary help needed
    By Computer_gal in forum Access
    Replies: 10
    Last Post: 04-16-2012, 01:18 PM
  4. Where to Put Instructor Salary?
    By alpinegroove in forum Database Design
    Replies: 54
    Last Post: 01-03-2012, 05:37 PM
  5. Per Record basis issue, NEED Help
    By PAS123 in forum Programming
    Replies: 2
    Last Post: 12-20-2011, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums