Calculating Employee Holidays Due - Very Complicated!!

[Ian Mac]

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.

The attached jpg shows what should be happening.

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.

Cheers all who contribute.

 

[Ian Mac]

Sorry all,

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)

Whoops, that was a big part to leave out.

CHEERS,

 

[mresann]

Ian, you have a very large project you wish done. While I'm happy to help out with individual questions through these forums, your needs approach that of a full scale programming gig. You yourself say you have a lot of factors to contend with, and from what I've seen it's a bit beyond the scope of a forum question and answer format. Others may disagree, but I usually can come up with an accurate time estimate. Something like what you have would take me at least 20 hours to create, implement and test, just to give you an idea.

Having said that, the first thing you really need to do is to break down the steps you need to perform. I counted no less than 5 major steps, each requiring recordset manipulation and function creation that would take considerable time to develop and test. The first thing I would do would be to flowchart your entire process using a program like Visio or any object developer. Once you do that, you will have specific processes which you can develop more easily. At that time, if you have coding snafus it will be easier to ask in the forum at that time.

Incidentally, I only develop with A2k or higher. I can do A97 but only if it can downgrade from my development application.

 

[Ian Mac]

Thank you for your comments,

However, you've hit a nerve, so apologies if what comes next is out of line.

I disagree that it is a "full scale programming gig", what is a 'gig' anyway, do you mean project I wonder.
This is actually 1 question relating to a the huge project that is already in place.
You will see from the Example DB that my tables and ground work are an excellent point to start from.

As for your idea of a forum??

and from what I've seen it's a bit beyond the scope of a forum question and answer format

I think it's....

A medium of open discussion or voicing of ideas

or

Any discussion group
accessible through a dial-in BBS (e.g. GEnie, CI$), a
mailing list, or a Usenet newsgroup (see network,
the). A forum functions much like a bulletin board; users
submit postings for all to read and discussion ensues.

I am an MVP and moderate on a different Forum to this one, I often sit down and chew over very large solutions for people, I asked the question here because it is an excellent Access Forum, the one I moderate is specific to something else. I don't see it as a hassle to spend more, sometimes, than your 20 hours or so (which I actually don't believe it would take at all) I find it a comfort that I am challenged and and rewarded by the extra knowledge.

Incidentally, I only develop with A2k or higher. I can do A97 but only if it can downgrade from my development application.

Bully for you. I have 97, 2000, XP, 2003 at home for developement. A strange comment you might say, then so was yours.
I don't feel I should justify why I need 97 to you, is your comment supposed to make me feel small?
But I will, we use it at work!

Finally, you usually come up with a accurate time estimate??? does this actually mean you sitting down and work out whether the problem may 'not' be worth your time in looking at. What very strange way of looking at things.
I'm sure anyone who has spent more than x amount of time helping you in the past would love to hear you say this.

I'm not wanting to start a thing here, so can you please not post a reply unless you suddenly gain more time.

I would, however love someone to give a pointer on the question I have. It is a challange, but I feel one which can be done.

 

[mresann]

Ian,

I'm sorry. After wondering what I left out, I noticed in your first post that you had a zipped file of a db below that of your jpg. I hadn't downloaded that one, just the jpg, so I had assumed (until now) that you were looking for a table design as well! Instead, you have a pretty well thought out third-norm table structure database just ITCHING to be queried!

Indeed, in your post you HAD mentioned you included the db, and I missed it as well.

I just wiped the egg off my face into the frying pan...all 3 of them (eggs, not pans).

OK... I do take back my recrimination, in fact beg your forgiveness.

Now that I'm back on track, I do want to mention that what you want has to be done in a module. So I will whip up something up. Let me make a couple of assumptions, though...

ASSUMPTION ONE: I assumed the calculation for anyone working in a PREVIOUS WORKING PERIOD worked 5 hour days, Monday through Friday. If you need to vary their work hours, then the code will have to be adjusted accordingly (and yes, very significantly).

ASSUMPTION TWO: I assumed that you want the previous work period to be included in figuring out the amount of holiday time per year worked. As such, I took the total days worked in the previous work period, subracted that figure from the Current Starting day, and came up with a "Calculated" starting date. Level 1 went from that date to the same date a year later - 1 day, and Level 2 went from the calculated starting date + one year to the same date 10 years later minus one day, and Level 3 (30 days) was from starting date + 10 years to whenever.

Example: Let's say an employee's current starting date is Sep 1, 2004.

He worked from Feb 1, 1997 to August 1, 1998

That is 180 days (actually, I'm estimating for this example, but the actual calculation would be the exact amount of days), so the current starting Date would be calculated at

Sep 1, 2004 MINUS 180 days =
Mar 1, 2004

So the Level 2 period (25 days holiday per year) starts on Mar 1, 2005 (instead of Sep 1, 2005), and the Level 3 Period (30 days holiday per year) would start Mar 1, 2013 (10 years after the calculated Starting date).

If that works for you then I'll post the db with a modified module so you can review it first thing in the morning your time (assuming you are UK, I'm near LA). There will be lots and lots of comments for everyone to see as well.

Again, my apologies for seeming crass when all I had to do was open a file. Next time I answer I'll be a little more observant.

 

[Ian Mac]

Hi,

thanks for getting back to me, and no harm done.

I have new information regarding this, but, I found the current Excel calculator on my works intranet at 21:55 5 minutes before I left.
When I'm in today I'll extract the rules they've put down as formulas.

They seem a bit odd regarding leap years and previous service so I think what's best is I bring those rules home and go from there.
We don't have internet at work so it will take longer than if I was just working at home, i.e. I'll have to bring it here.

Regarding your to assumptions.

1) It make no difference what hours and over how many days they worked in any previous role.
Given your example 1st Feb - 1st Aug, if they work every Sunday for 2hrs and nothing else, they still have 180 days service.

2) It works pretty much like you say.
The way I was starting to look at this (and it may be the wrong way) is saying:
They start 1st Sept 04, I was thought of adding the remaining 185 to the start date. StartDate+(365-180) = 185.
Therefore 5th May is there aniversary day, regardless of what year, that is the day which would trigger change, or indeed no change.

My theory was to have a row return in the query with the day before this @ x days and the day itself @ x days.
Regardless of whether there is a change.
So if they have 4.5 years service the row return from the aniversary would be @ 25 days and at 25 days.

Does that make sense?

Anyway, I'm rambling and I do think that your suggestion of a full on Function would be a better way to do this.

Hope that gives you more infomation, I'll get the rules back today, I don't tend to post until 10am everyday, but I'll try and get this back soon.

Thank you for your time.

Cheers,

 

[Mile-O]

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.

First of all, I would not use 365 as a constant due to the occurrence of leap years. I'd calculate this by using the DateDiff() function based on the first and last dates of the year.

 

[mresann]

This module is taking a bit longer due to the Previous Employment factor.

Say someone started on December 15th, 2004, on his current employment. If he had previously worked for 6 months back, I have re-calculated his starting date to June 15th (approximate; the actual calculated date may be different as it uses the amount of days, not months). If he has a "LeaveYear" date of November 30, should that be included for 2004, even though he wasn't working on that day? (Actually, he may have been working on that day depending on the actual dates of previous employment and the happenstance of the LeaveYear date, but that is not significant for this question).

I'll hold off programming for it until you say so, it is a rather significant lemma that needs to be determined. Everything else seems to flow in place rather easily.

By the way, the formula SJ McAbny refered to has been taken care of in the module, indeed using date differential functionality.

 

[Ian Mac]

Hi,

I'm only in for 2 minutes so I don't have the rules yet.
However 365 is the calculation, regardless of leap years.

This is the first thing I queried way back in Jan last year.

As for service I believe that 1 full leap year, 366 days, equates to 1 year 1 day previous service.
This is in the rules I have but not had time to look at them.

I'll have all the factor tomorrow.

Cheers,

 

[Ian Mac]

Hello,

I finally have the rules, or should I say had the time to post them.

They're actually pretty simple.

With regard to current year Annual Leave, as mentioned, all calculations are based on 365 days.

Previous service is calculated as days (StartDate - EndDate + 1) and then divided by, again, 365.

So if a person starts on 01-01-05 and has 1 leap year of service (366 days) the aniversary date is 31st Dec (01-01-yy minus the extra days service, i.e. service year is 365 - 1) for all future years within current employment.

Attached is a more up to date version of the Database, I've added a new query for service (this may be no good to you but it has the rules applied).

Hope that makes sense.

 

[mresann]

Here is my first attempt at what you want.

I haven't incorporated the 365 day calculation as of yet, that may take a little more time.

I have created a module ("Module1") with several functions. The main function is called

fncCalculateHoliday

and takes one or two variables. The first variable is the Employee ID number. The second variable is optional, and is a boolean which allows you to factor in previous employment, if the employee had worked previously.

Since you didn't include any tables that housed any data relating to previous employment, I created a test table called "tbl_PreviousDate". The table contains random periods of previous employment for some of the employees in your main table.

To run the function, open the module Debug Window (in A97). Next, type in the function name. Pass the employee ID number in Long datatype format, and if you want to include previous work history, pass the optional boolean as True. The boolean defaults to False, or no inclusion of previous history.

Your debug window should display information similar to the excel sheet you displayed in the image file.

If you need help with the code, let me know. I'll still take a gander at the 365 day business rule, as well as tighten the duplicat code structure in the main function.

 

[Ian Mac]

Blimey,

I taking this to work NOW!
Looks exciting, I'll post back tomorrow.

Good work fella.

 

[mresann]

OK, I fixed the the section that identified the Leave year label ("Leave 03" for end of 2003 leave period, etc).

I also have instituted a function that allows leap years to decrement a day from leave and years worked calculations (ie, your 365 day requirement for all years).

However, I'll hold off on posting until you respond so I can incorporate anything that you find out from your end.

Have a good weekend...

By the way, what is a Scot doing saying "Blimey"? Sort of like a New Yorker saying "Y'all come back now, y'hear?" with a southern accent.

 

[Ian Mac]

OK, I fixed the the section that identified the Leave year label ("Leave 03" for end of 2003 leave period, etc).

I also have instituted a function that allows leap years to decrement a day from leave and years worked calculations (ie, your 365 day requirement for all years).

However, I'll hold off on posting until you respond so I can incorporate anything that you find out from your end.

Have a good weekend...

By the way, what is a Scot doing saying "Blimey"? Sort of like a New Yorker saying "Y'all come back now, y'hear?" with a southern accent.

Blimey

I took your DB work to...well...work.
I must say I haven't had time to really digest what is going on, we have the auditors in on Monday so it's been battle stations.

I do have a question though, I can't see how I'm eventually going to utilise this in the way I hoped.
This in it's self isn't a problem, an answer is an answer and if it works thens I'm in debt.

Can you explain how it wil all come together, I was hoping for a Query which used a function to return every years leave for each employee.
So it would be a list of employees and holiday balances for each year inc. current.
i.e.

Ian Mac 02 [hh]:mm
Ian Mac 03 [hh]:mm
Ian Mac 04 [hh]:mm
Jane Doe 01 etc.

I would then query that for each year.
Does this do this? As I said, I haven't fully understood what is going on with it, I'm also not the best programmer in the world, you may have guessed.

The reason for this end result is that I have an Excel solution in place which pulls in the information and links it with holidays taken/booked.
I wish I had a copy here to show you as it is a real marvel in itself.

I hope they aren't too taxing a set of questions, I'm just trying to get my head round it all.

Thank you very much for the work which has gone in so far.

Cheers,

 

[mresann]

Here is the next file.

Two subs in Module 1, subExportWorkData and subExportLeaveData, send the data to two temp tables. I'm sure you can create a report out of it if you want. I originally was going to create a function to include all of the employees in one table, but decided to let you create one employee record at a time.

The form that opens up initially lets you choose the employee. If you need more help holla.

 

[Ian Mac]

HI,

I'd better reply to SOME posts to earn this one back.
I still haven't understood any of it, those auditors are really having fun!!!

I do promise faithfully that I will understand ALL of it in time, no point otherwise.

I have a query regarding my own data (see attached)

The data I have put in is:

My Start Date: 08-12-03

My Hours:
08-12-03 to 25-01-04 = 25p/w
26-01-04 to Blank (i.e. present) = 37p/w

My Contractual Year (i.e. End of leave year): 30-11-04 (this generally should work as 30-11 every year)....(Sorry 11-30 for you americans )

My contract end date: 24-03-05

the attached doesn't show this, my hours are 44.######.

I would have expected to see:

08-12-03 | 25-01-04 | 25 | 22 |---> Workings
26-01-04 | 30-11-04 | 37 | 22 |---> Workings
01-12-04 | 07-12-04 | 37 | 22 |---> Workings
08-12-04 | 24-03-05 | 37 | 25 |---> Workings

The Employee can have the holiday allowance up to the contract end date.
I did orginally say that they can't see there service until the day they hit it but it looks like you've done enough for me, so as far as I'm concerned they can have it.

Also, does it matter if the previous button is clicked?

And....(I I'm starting to feel your 1st comments were justified )
I would like to see all of the employees in one table, I can then query that.

The issue with this (NOT YOUR STUFF BY ANY MEANS) is that the report MUST be exactly as the paper one they use.
I cannot recreate it in Access as the 'behind the scenes' calculations are enormous.

Also (again), do you have the opportunity/availablity to explain some of the workings via chat/voice sometime? That would be excellent.

And (again) I hope your enjoying this, it's no fun listening to me ask, ask, ask if your not.

Thank you kindly,

 

[Ian Mac]

BTW, works VERY fast. Thank you

 

[MsLady]

~Nice thread Bumping this up, as i'll need it soon.