Holiday Dates

[Michael Gill]

I am having trouble in getting my head round dates specifically employee
holiday dates where an employee has holidays booked for the year which are
added to and deleted from.

1. I need to allocate an allowance for existing and when sombody joins
calculated on the number of months left in the holiday year would i have a
seperate table tblHolidayEntitlement with(EmployeeID,Year,DaysEntitlement)

2. I need to report on holidays by departmemt which would be like a spread
sheet a column for date and for each employee in that department.

Would i have tblHolidays with (EmployeeID,Date) linked to tblEmployees which
is linked to tblDepartments and also link tblHolidays to tblDates(DateID
=01/01/2009 just being dates.

Michael

 

[Klatuu]

Since I don't know your location, I will have to ask for your definition of
"holiday".
In the U.S. holiday means specific days of the year like New Year's Day,
Christmas, Independance Date, etc, that are days the business is closed and
everyone is off. Days an employee takes off from work are traditionally
know as Vacation days. But, when calculating vacation days used, days in
the holiday table are not counted as vaction days, they are counted as
holiday days.

But, as I understand it, in the UK (and maybe Australia) the word holiday
means what we in the U.S. call vaction.

So, based on your description, I am assuming, by holiday, I would understand
it a vaction.

Now, to accomplish this, you will need a couple of tables. One would be a
holdiay table (U.S. definition) and another would be a vacation table.
The holiday table just needs a record with the date and the description of
the holiday for lookup purposes.
The vacation table would need to have the date and the employee table's
primary key as a foreign key to show the date the employee is taking off.
That is one record per day. Don't even think about a start and end date
version. That only makes it harder.

As to #1. That is a calculation and should not be stored. I am sure your HR
department as a formula they use to determine an employee's eligibility.
They are usually based on a number of days per month employeed and some
other rules that usually apply to new hires, but that is more writing code
to determine the eligibility than it is about data storage. You may be
tempted to store the calculation, but don't. Calculate it when you need to
know.

As to #2. Create a query that transforms the data into the form you need to
use a the report's record source.