All,
OK, I'll give this a go and if I leave anything out please post back with questions.
I've been putting this on hold for oooo 8 months.
The basics are that I need to calculate Employee holidays based on LOTS of factors.
Here are the key elements:
tbl_EmployeeMain
1) StartDate - the date the employee started to work for us.
2) LeavingDate - the date the employee stopped working for us.
tbl_LeaveYearEnd
3) LeaveYear - This is the date that an employee's leave year ends
Note: this is generally 30th November, however they can opt for a different date
tbl_EmployeeHours
4) HoursStartDate
Note: If it is the Employees 1st hours pattern then the HoursStartDate will be blank and the tbl_EmployeeMain!StartDate is used
5) HoursEndDate
Note: is blank if they leave, tbl_EmployeeMain!LeavingDate is used
The above is the elements I currently have, I have worked out how to calculate holidays on the above, but the next part throws it all off.
------------------------------------------------------------------
Holidays are calculated like this:
(([Days]/365)*[Hours]*22*7.4)/37
Green = Constants, always those numbers
Purple = Calculated
Orange = The hard part - is how many days holiday someone is entitled to pro-rata per annum.
Days (Orange) a year are work out like this:
0 - 1 year service = 22 days i.e. 1st Jan 03 - 31st Dec 03
1 - 10 years service = 25 days using above 1st Jan 03 - 1st Jan 04 = 365 days @ 22 and 1 day @ 25
10 + = 30 days
So using the above info:
Jim Brown Started 27th Oct 03 and works 25 hours during training for 2 weeks,
on 10th Nov after training is put up to fulltime at 37hrs p/w,
Jim then has a baby to look after and drops his hours to 20 p/w starting Mon 2nd Feb 04 then again to 10p/w on 29th Oct 04, he is due to leave 29 Apr this year.
Basically it should be:
Year Decimal Hours Hours
Leave 03 - 13.58575342 - 13:35
Leave 04 - 107.1484932 - 107:08
Leave 05 - 20.54794521 - 20:32
I realise the above may not make sense so question are welcome.
I have attached the raw DB with what I have, I'm not even sure this is a Query Question or I could use a function.
Oh, here's the other thing.....................Jim doesn't get to see his service balance until he has completed his full year, i.e. the projected hours will be on 22 until 28th Oct then he will se it at 25.
I REALLY hope I can get this monkey as it's driving me nuts.
P.S. This needs to be done in Access 97, cheers.
-----------
Just thought of something else,
An employee may have previous service.
I can add a table which has this in days but it will need to be calculated in.
SO, Jim used to work for us from 1st Nov 88 - 22nd Jul 92.
His service is 1360 days, this can be stored in a seperate table.
BUT! that does mean his service year is now 18 Jul (I think, based on quick calculations and NOT having spoken to HR about this)
I can email my starting point DB to anyone who wants a stab.
Se below for expected outcomes from a Query.
Cheers all who contribute.
OK, I'll give this a go and if I leave anything out please post back with questions.
I've been putting this on hold for oooo 8 months.
The basics are that I need to calculate Employee holidays based on LOTS of factors.
Here are the key elements:
tbl_EmployeeMain
1) StartDate - the date the employee started to work for us.
2) LeavingDate - the date the employee stopped working for us.
tbl_LeaveYearEnd
3) LeaveYear - This is the date that an employee's leave year ends
Note: this is generally 30th November, however they can opt for a different date
tbl_EmployeeHours
4) HoursStartDate
Note: If it is the Employees 1st hours pattern then the HoursStartDate will be blank and the tbl_EmployeeMain!StartDate is used
5) HoursEndDate
Note: is blank if they leave, tbl_EmployeeMain!LeavingDate is used
The above is the elements I currently have, I have worked out how to calculate holidays on the above, but the next part throws it all off.
------------------------------------------------------------------
Holidays are calculated like this:
(([Days]/365)*[Hours]*22*7.4)/37
Green = Constants, always those numbers
Purple = Calculated
Orange = The hard part - is how many days holiday someone is entitled to pro-rata per annum.
Days (Orange) a year are work out like this:
0 - 1 year service = 22 days i.e. 1st Jan 03 - 31st Dec 03
1 - 10 years service = 25 days using above 1st Jan 03 - 1st Jan 04 = 365 days @ 22 and 1 day @ 25
10 + = 30 days
So using the above info:
Jim Brown Started 27th Oct 03 and works 25 hours during training for 2 weeks,
on 10th Nov after training is put up to fulltime at 37hrs p/w,
Jim then has a baby to look after and drops his hours to 20 p/w starting Mon 2nd Feb 04 then again to 10p/w on 29th Oct 04, he is due to leave 29 Apr this year.
Basically it should be:
Year Decimal Hours Hours
Leave 03 - 13.58575342 - 13:35
Leave 04 - 107.1484932 - 107:08
Leave 05 - 20.54794521 - 20:32
I realise the above may not make sense so question are welcome.
I have attached the raw DB with what I have, I'm not even sure this is a Query Question or I could use a function.
Oh, here's the other thing.....................Jim doesn't get to see his service balance until he has completed his full year, i.e. the projected hours will be on 22 until 28th Oct then he will se it at 25.
I REALLY hope I can get this monkey as it's driving me nuts.
P.S. This needs to be done in Access 97, cheers.
-----------
Just thought of something else,
An employee may have previous service.
I can add a table which has this in days but it will need to be calculated in.
SO, Jim used to work for us from 1st Nov 88 - 22nd Jul 92.
His service is 1360 days, this can be stored in a seperate table.
BUT! that does mean his service year is now 18 Jul (I think, based on quick calculations and NOT having spoken to HR about this)
I can email my starting point DB to anyone who wants a stab.
Se below for expected outcomes from a Query.
Cheers all who contribute.
