Jump to content
Salesforce and other SMB Solutions are coming soon. ×

vacation day and sick day record keeping


Recommended Posts

I am relatively new to setting up FM databases, but have been asked to set up a database to track vacation earned from date of hire (doh), vacation taken (showing actual dates), and vacation days remaining. Vacation days do not carry over from year to year and must be taken before Dec 31st (jan. 1 to Dec. 31st). Vacation is earned as follows:

Length of service from doh Vacation earned

6 months 2 days

7 months 3 days

8 months 4 days

9 months 5 days

1 year 5 days

2 year 10 days

3 year 15 days


Vacation time must be taken in minimum increments of 4 hours, i.e. 4 hrs, 8 hrs, 12 hrs. etc.


I want to set up a database that also tracks sick days earned, sick days taken (showing the dates) and sick days remaining. Sick days are earned at a rate of 5 days per year after completion of 90 days from doh. Sick days may accumulate to a maximum of 15 days.


If anyone can help me with setting up the calculations for these and fields, it would be much appreciated.

Link to comment
Share on other sites

The first thing you need to think about is the table structure required. What tables have you set up and how are they related?

Link to comment
Share on other sites

I have set up the following fields:

vacation days earned

date of hire

Vacation days taken

vacation days remaining


sick days earned

date of hire

sick days taken

sick days remaining


I have not set up any relationships.

Link to comment
Share on other sites

Well obviously you need a Staff table - records of each staff member.

Then you need a Leave table - record of each period of leave.

These tables are related first by StaffID


In the Leave table, you would have fields for start date and end date. But from your description, you also need start and end times. You would calculate each leave period from the start and end dates and times. This would probably be in half days (4 hour blocks). I don't know how you currently calculate that - please advise.


In the staff table, you can calculate how many half day blocks a staff member has had by summing the related leave.


Also in the staff table, you would have a hire date field. From that, you would calculate the period of hire by comparing it to today's date or to a static date (say in a global field). The period of hire would need to be calculated in whole months (rounded down). From that, you could calculate how many half days they were entitled to. That could be compared to how many they have taken as leave.


I could go on but I don't know how much of the above you are understanding. Let me know.

Link to comment
Share on other sites

Personal File:


date of hire

vacation days earned = sum(TimeClock::vacationDaysEarned)

Vacation days taken = sum(VacationTable::vacationDays)

vacation days remaining = earned - taken


sick days earned = sum(TimeClock::SickDaysEarned)

sick days taken = sum(SickDays::taken)

sick days remaining = earned - taken


A first draft suggestion.

Link to comment
Share on other sites

  • Create New...

Important Information

Terms of Use