Employee Absences - Can Access Solve This Problem ?

[Arundeln]

Hi I an new to the forum, and I am not sure whether this is achievable in access, and if so, whether someone could provide me with a solution. A sample database of dummy data is attached.

Here is the problem

1) we have a daily clocking on and out system which records
absences/attendance at work as individual lines of entry. I am trying
to identify and count separate periods of 'sick related and
unauthorised absence entries' from a whole list of absence entries.

2) This is made even more difficult in that some of these absences
straddle the weekend, but as Sat/Sun is not a working day then the
entries are missing and yet such absences should only be regarded as
one single absence. The same could be said of absences straddling a
Bank/Public Holiday.

3) My reason for identifying SICK RELATED AND UNAUTHORISED ABSENCES is to determine whether the employee triggers consideration for a warning.

4) Both these absence types (sick and unauthorised absences) are
strangely treated the same, as invariably an absence entry is tagged
as unauthorised (but is either immediately 'preceded' or 'followed' by
a sick absence entry) and yet in reality is a single continuous sick
absence as the unauthorised element is simply because the system has
not been updated with a medical certificate.

For info Sick Related Classifications are:-

Industrial Injury
Sickness
Long Term Sick
Unauthorised Absence
Unauthorised


5) In a nutshell I am looking to identify and put a count on separate
periods of continuous sick absence/unauthorised absences for each
employee.

6) What I would then like to do is produce a 'sick related and
unauthorised absence' report that lists each employee together with
the 'count' and then underneath each employee the relevant lines of
absence entry as well as inserting the warning (see separate warning
table) entry at the appropriate point in the absence entry date
history.

7) The report is then ordered by the employee with the most counts

This will allow me then to manually glance at the report to work out
if someone has breached the standard.

I suspect that access could actually be designed to actually pick out
those who breach the standard automatically but I don't want you to
spend too much time on this

I attach the database with dummy data

Hopefully I have explained this well.


Thanks in advance

 

[DavidAtWork]

To get you going on this, one approach could be
Define your annual period, whether it be calendar, financial or academic, but you'd need a table of these for each year unless you're only dealing with calendar year.
You'd also need a table of public holidays (PHs) so that you can ignore days in sick periods that straddle PHs. With these in place it would be a matter of taking each employee in turn and querying all sick periods in the defined annual period. For each period you'd go from the start date to the end date, testing each day in between to see if it's a weekend or PH, if not add one to the sickDay count. You can have different sick day counters for each type of Sick Related Classifications and increment accordingly as per absence type. When all sick periods for that employee have been processed you will have a total of all sick days for each type of absence.

As for the method to do this I would write functions
1) that would calculate if a given date was a weekend or PH, pass to it the date and it would return True/False
2) the main function that would work through a recordset of employees (outer loop) and for each employee (inner loop) you work through a recordset of all sick periods within your defined annual period. For each sick period, you'd have to test each day from start date to end date using function 1) and update the relevant absence type counter.

I would imagine you'd need some kind of absence summary table that would record the year, employeeID, and totals for each of the absence types. This table would be updated after each employee has been processed in function 2) above

Lots to think about as there many events and variables to monitor as the process executes.

David

 

[jdoathout]

In the long date format you could set the query to exclude saturdays and sundays however holidays would need to be through VBA