I have been bitten once again by our Human Resources department in my quest to come up with code to properly update a table based on Employee attendance policies. Thanks in advance.
OccurrenceID Auto Number
[Occurrence Value] Long Integer
[Occurrence Type] Text
[Occurrence Comments] Text
[Occurrence Dropped] Yes/No
[Occurrence Reference] Yes/No
1. Go through all records in tblOccurrence and find the ones that are older than one year from the current date.
In the found records mark the field [Occurrence Dropped].
2. Go through all records in tblOccurrence and find the ones that have a value of 0 in [Occurrence Value].
In the found records mark the field [Occurrence Dropped]
3. Find all records by Employee in the current year (current year is 1 year back from the current date) and find the most recent record (by Date) with the [Occurrence Reference] marked and select all records after. If nothing is found, keep the found records for the employee for 1 year back.
Search through the records for a 4 month span of values in the [Occurrence Value] field greater than 0]
If nothing is found then do nothing.
If one is found (searching from the oldest to the newest) then add a record with the appropriate
Employee, Ending date of the 4 month span , Value of -1 , Type as Award, Comments
of "Attendance Award", Dropped as No, and Reference as Yes.
NOTE: The attendance policy allows no more than 3 occurrences can be dropped in a year. So the 4 month spans can not overlap.