vacation day and sick day record keeping

[ragman9055]

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.

[David Head]

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

[ragman9055]

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.

[David Head]

Sorry but you are not going to do this with one table.

Or at least you shouldn't do this with one table.

[ragman9055]

Any suggestions would be much appreciate in setting this up. Thanks!

[David Head]

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.

[Jack Rodgers]

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.