UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Design Logic Help Needed, Access 2010    
 
   
LilAnnCC1
post Dec 6 2017, 08:44 AM
Post#1



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


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

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?


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 08:54 AM
Post#2


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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".

--------------------
Go to the top of the page
 
LilAnnCC1
post Dec 6 2017, 09:13 AM
Post#3



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


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?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
nuclear_nick
post Dec 6 2017, 09:15 AM
Post#4



Posts: 1,401
Joined: 5-February 06
From: Ohio, USA


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!

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 09:18 AM
Post#5


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2017, 09:19 AM
Post#6


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


The system is designed to make HR indispensable.

--------------------
Go to the top of the page
 
LilAnnCC1
post Dec 6 2017, 09:33 AM
Post#7



Posts: 719
Joined: 31-May 04
From: Wisconsin, USA


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!


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
kfield7
post Dec 6 2017, 12:49 PM
Post#8



Posts: 775
Joined: 12-November 03
From: Iowa Lot


How are short term and long term disability handled?
Go to the top of the page
 
Art_Of_War
post Dec 7 2017, 04:18 AM
Post#9



Posts: 12
Joined: 4-December 17



LOL @nuclear_nick, your witty post made my evening. *I* care about your Access projects--care to show me one of them some time? smile.gif
Go to the top of the page
 
nuclear_nick
post Dec 7 2017, 08:10 AM
Post#10



Posts: 1,401
Joined: 5-February 06
From: Ohio, USA


Ain't got one. Got 45 FE's connecting to 240 BE's covering 7 in-house warehouses and 5 3rd party warehouses. I can't say how many users are connected at a time. Haven't verified all the active users, but there are 1094 in the 'users' table that say they are active. I could probably query the activity log for more verification.

And this is all Access.

I do a lot of things the hard way. fundrink.gif
This post has been edited by nuclear_nick: Dec 7 2017, 08:10 AM

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:29 PM