Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Anaheim, California, USA
    Thanked 2 Times in 2 Posts

    Totalling (2000/SR1)

    I have a database that keeps track of employee attendance. I didn't make the rules, I just put together the database.

    The employees get points for various infractions, unexcused absences, late arrival, early departure, etc. The points totals are based on a rolling 12 month calendar, so I set it up to calculate their points via a text box, since it can change every day, everytime the employee form is brought up. The employees can also earn a bonus of minus one point for a quarter of perfect attendance. The rules state that their totals will not drop below zero, so even if they haven't gotten any points and then earn a bonus point, they will still only have zero.
    I set up the text box with the following formula =iif(sum([points])<0,0,sum([points])). If the total is less than zero, it shows zero, just what we wanted.
    Now we are getting to where people are earning their attendance bonuses, and now I am running into a problem. The problem is, if they are awarded the attendance bonus, which drops them below zero, let's say they had a half point, earned the bonus, which technically drops them to a negative half point, then a given a point, under the rules they should have 1 point, but the system will show them with a half point, (negative half point plus one point = half point). I thought it would be fairly simple to account for that, to make it even more complicated, what if someone earns 4 negative points, (4 quarters of perfect attendance) then is given a point for an unexcused absence they should have 1 point.
    I hope this makes sense, does anyone have any ideas how to account for all of this? Thanks for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 30 Times in 30 Posts

    Re: Totalling (2000/SR1)

    If positive and negative points incurred/earned can compensate each other over a period of time, you can add everything together and cut off at zero. But apparently this is not the case, from your example with 4 bonus points and 1 infraction resulting in a score of 1. If positive and negative could compensate each other within a period of time, the score would be -3, hence 0.

    I would say that you have to recalculate and store the score after each point incurred or earned, cutting off at zero at each calculation, instead of at the end.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts