Help with dates.. Auto adjust table values at anniversary..

[Regression]

Hello All,

I am trying to create a way to handle employee vaction. I would like the database to run a process daily (Perhaps on open). That will check all the employees start dates and if they match the current month/day reset the employees vacation.

I am not having much luck and see a few problems here. No one is in the office on weekends so the database will not be opened on weekends and those individuals who's day/month fall on a weekend will not be given thier appropriate vacation time.

I am really not sure how to pull this off. This is the first step in a multistep process. I will also need the database to evaluate the lentgth of employment so that it gives the appropraite amount of vacation days on the employees aniversary date. (Vacation hours are based on a sliding scale)

Has anyone ever done anything like this? I would appreciate input. Anything to get the creative juices flowing would be wonderful.

Thank you

 

[Darrylles]

Hi,

There will be many ways to do this (creative juices contain different chemicals for different people lol).

I'd say for this to work - you need an additional table for holiday entitlement - linked to employees.
In here you'll have entitlement-check-date (which is the date that their entitlement changes/clicks over to new year), and last-update which stores last date updated (to cover those holiday/weekend periods).

So:

tblVac_Check
pk (autonum)
emp_id_fk (link to employee)
check_date (date)
last_update (date)

Now create a totally different application - this will contain a simple form that is activated by MS-Scheduler or whatever.
It will also contain a Link-Table to the above table stored in your main database.

The simple form should on running, do the following:
a)Get todays date and load all tblVac_Check records.
b)Compare today with Check_Date. If = to or later than check_date - then...
c)If Last_check < Date_Check (must have been checked a year ago).
d)Dependant on b & c - update entitlement (carrying over spare holidays if reqd), set Last_Checked to today, and Check_Date to Check_Date + 1 year.

MS Scheduler (or similar) will run this every day that it is activated and the logic 'should' work over weekends/long company shut-downs.

This is an 'idea' and will need polishing.

Hope it helps,

Regards,

Darrylle






&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot; darrylles@totalise.co.uk