Results 1 to 2 of 2
Thread: Totalling (2000/SR1)
2004-07-07, 18:35 #1
- Join Date
- Aug 2001
- Anaheim, California, USA
- Thanked 2 Times in 2 Posts
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.
2004-07-07, 19:02 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 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.