Printable Version of Topic

UtterAccess Forums _ Access Tables + Relationships _ Design Logic Help Needed

Posted by: LilAnnCC1 Dec 6 2017, 08:44 AM

We have a complicated "Point" system regarding employee absences. It's only complicated because management makes it so.

Employees having unexcused absences get Points assigned. After an accumulation of X number of points, they get a written warning. When an employee reaches the magic number of 24 points, they are terminated.

Management implemented this new point policy on 11/1/2017 and everyone employed on that date has to go until 11/1/2018 without an accumulation of more than 4 points in order to earn a "Free" paid day off. Employees hired after 11/1/2017, start on their hire date. Points disappear from Employee's totals 1 year after the date the point was given. In other words, if I got a point on 11/2/2017, that point would not be counted in my total as of 11/3/2018.

Warnings need to be given when the accumulated points total 12, 16, 20 and 24 (Termination). The Absence table consists of:

ABID (PK)
EmpID (FK)
AbsentDate (Date)
AbsentReasonID (FK)
AbsentHours (Number--total number of hours absent)
AbsentPoints (Number--Point(s))

I need a way to generate warnings at the above intervals. Once the employee receives a warning, they can't receive another warning until they reach the next threshold. This is where I am stuck. How would you tackle this?

Posted by: GroverParkGeorge Dec 6 2017, 08:54 AM

You need to include a field for the date on which a warning was issued, so you can use that date to calculate whether the points accumulated after that date add up to another warning. Actually, that would probably be in a separate table of "WarningsIssued".

Posted by: LilAnnCC1 Dec 6 2017, 09:13 AM

Are you saying add a date field for warnings in the absence table as well as create a warnings issued table? I do have a discipline table that would hold an absence warning. Should I have a separate table for just absence warnings regarding the points?

Posted by: nuclear_nick Dec 6 2017, 09:15 AM

And then, if it's like where I work, there's a difference between when the warning level was reached and when the employee knew about it, so that's another field...

Two things I'm learning to dislike... IT (specifically those who don't care about my Access projects) and HR.

In all seriousness... I'm with GPG. You can check if the warning date was between the beginning of points accumulation and points resetting.

Back to comedy... ours has a different system... the 'review period' is fiscal, while PTO is reset Jan. 1st.

Fun fun fun!

Posted by: GroverParkGeorge Dec 6 2017, 09:18 AM

No, one date is fine. It's the date you need to use in your calculation as the basis for determining whether any given absence occurs before or after that warning date. If the absence date occurs after a warning date, but before the next level of warning is reached, don't issue a new warning. If the absence date occurs after a warning date AND the points reach the next level (24?) then a new warning is issued. Also, you'll need to include that time period as part of the calculation, but I think you are already accounting for that.

This can all be done in a query, btw.

Posted by: GroverParkGeorge Dec 6 2017, 09:19 AM

The system is designed to make HR indispensable.

Posted by: LilAnnCC1 Dec 6 2017, 09:33 AM

LMAO, GPG and Nick! You have obviously met my HR person!

Okay, I'm off to add a warningdate to the Absent table and see if I can put together a query to pull it all together!

Thank you both for direction!

Posted by: kfield7 Dec 6 2017, 12:49 PM

How are short term and long term disability handled?