Record Sick Leave via access

[jzwp11]

Are all of these stats on a monthly basis?

I'm not sure how to calculate some of these in terms of your process, so you will have to help out.

Total number of staff on vacation Do you mean how many have taken at least 1 day of vacation or off for the whole month?
Total number of vacation days accumulated for the month Do you mean accumulate the time taken as vac time for the month or do you want the total of vac time that still has not been used?
Total number of officers on vacation? Do you mean the total number of different officers who took at least 1 day of vacation during the month?
Total number of civilians on vacation Same as above
Average number of staff on vacation per day An example would be good
Peak day and number of persons (Day with the most persons on vacation and total for that day)
Minimum day and number of persons (day with least persons on vacation and total number for that day)

Accumulated Sick days for the month: Do you mean accumulate the time taken as sick time for the month or do you want the total of sick time that still has not been used

Number of officers on sick leave For any time during the month? If 1 office takes 1 day off and another takes 2 then each would be treated equally and the count of officers on sick leave would be 2? Or do you want only those on sick leave for the entire month?


Number of civilian on sick leave: Same questions as posed for the officers.
Number of officers on excessive sick leave for the month. Do you mean the number who have exceeded their allocated amounts or do you have a monthly limit as well?
Number of civilians on excessive sick leave for the month Same questions as above
Average number of sick per day: Do you mean accumulate the time taken as sick time for the month or do you want the total of sick time that still has not been used
Peak sick day and number of persons: The one day in a month where the most people were sick? Again do you want it broken down by civilians and officers or combined?
Minimum sick days and number of persons. Not sure what you mean by this.

How do you treat weekends? Is every day considered a work day?

[Nixx1401]

Total Number of staff on Vacation - Number of staff on vacation for the month even if their vacation extends into the next month and also the staff who took individual days eg 1, 2, or 3 days.
* If a staff member vacation extendes into the next month he/she would be tallied in the next month tally for being on vacation


Total Number of Vacation Days Accumulated - Time taken as vacation time for the month

Total number of officers on vacation -Total number of Officers on vacation including those who took individual days

Total number of civilians on vacation - Total number of civilians on vacation inlcudiing those who took individual days.

Average number of staff on vacation per day - Total number number of staff on vacation for the month (individual days and extended vacation) divided by Number of days in the month.

Accumulated Sick day for the month - the time taken as sick time for the month

Number of Officers on Sick Leave - For any time during the month? If 1 office takes 1 day off and another takes 2 then each would be treated equally and the count of officers on sick leave would be 2? Yes this is correct

Number of Civilians on Sick Leave - The same as above

Number of Officers on excess sick leave - Total number of officers who have exceeded their allocated amounts

Number of Civilians on excess sick leave - same as above

Average number of sick per day - Total number of staff on sick leave divided by the Number of days in the month

Peak sick day and number of persons - Day with the most persons sick and total for that day

Minimum sick day and number of persons - day with least persons sick and total number for that day

I hope these clears up everything if not please email me.

thx

[jzwp11]

Peak sick day and number of persons - Day with the most persons on vacation and total for that day

Minimum sick day and number of persons - day with least persons on vacation and total number for that day

What happens if there are multiple days where the condition exists (i.e. a tie); for example let's say that 5 days all have the same # of people off (sick or vacation), do you want all reported? Same for the minimum, do you want all reported?

How do you handle weekends and holidays? Do they count in the total number of days in a month or do you only want business days. If someone is sick over a weekend does that count towards their allotment?

[Nixx1401]

5 days all have the same # of people off (sick or vacation), do you want all reported? Same for the minimum, do you want all reported? Display all the days and the total same for minimum.

If someone is sick over a weekend does that count towards their allotment? Yes

[jzwp11]

How do you handle weekends/holidays? Are all considered working/business days even for civilian employees?

[Nixx1401]

Actually since the dates of holidays change yearly I was not going to consider that, I was saying the user will have to manually subtract the total for the month. If you have any other solution.

Weekends are counted as a regular day for vacaction and sick leave. If a person has 14 days vacation it is 14 days inclusive of the weekends same for sick and for all staff whether officer or civilian.

[jzwp11]

Actually since the dates of holidays change yearly I was not going to consider that, I was saying the user will have to manually subtract the total for the month. If you have any other solution.

There are some custom functions out on the web to handle business days/holidays. I think one of those functions can be modified for use in your database to just take care of the holidays. It will require having a table with the list of holidays & their respective dates. Can you supply a list of 2011 holiday dates for your location?

[Nixx1401]

Sorry for the delay in response was a little busy today.

New Years Day 1st Jan
Errol Barrow Day 21st Jan
Good Friday 22nd April
Easter Monday 25th April
Heros Day 28th April
Labour Day 1st May
Whit Monday 13th June
Emancipation Day 1st Aug
Independence Day30th Nov
Christmas Day 25th Dec
Boxing Day 26th Dec

[jzwp11]

Thanks for the list. How do you want to see the stats, in a form or report?

[Nixx1401]

in a report

[jzwp11]

I've attached what I have so far. Behind the button on frmStat is some code that creates a couple of custom queries that are then used by a series of domain aggregate functions in the report to populate the statistics.

The last two items (Peak sick day and number of persons and Minimum sick day and number of persons ) are a little trickier since you do not store individual dates but rather date ranges for each person. I think it will require some custom code to get to a solution. One additional question regarding Minimum sick day and number of persons, if nobody takes sick time on a day would you consider that a minimum sick day since zero would be the minimum or do you want the day where at least one person was sick?

[Nixx1401]

zero would be the minimum. I took a look at what you have done and I am very impressed.

[jzwp11]

As I mentioned, those last two items will be tricky. I'll have to think about how to handle them. I'll post back when I come up with something (hopefully early next week).

[jzwp11]

I had some time this morning and was able to come up with the attached. I ended up using a temp table and then created a couple of queries based on that table that I used for subreports in the main statistics report

[Nixx1401]

Thank you will look at it.