Absence Management

[ltalbott]

Hi I have been asked to do some complex analysis on absence management. The problem is our HR System reports absence by each week. So for example someone who has been off for 5 weeks will have 5 different records, but these are continuous (eg.1st record starts on Monday and finishes Sunday, 2nd record starts Monday and so on).

I want to analyse the data in Access, but is there any way I can determine if the absence is continuous - in otherwords if the difference between the 1st record end date and the 2nd record start date is 1 then show the absence as one period, and so on.

Not everyone will have more than one record so is there an easy way to identify those staff with multiple records?

Any help will be greatly appreciated! Thanks

 

[Vonnie]

Could you show or describe the data design with more detail?
Like?:
[Tom] [Week1] [5] (absences)
[Tom] [Week2] [3]
[Mary] [Week3][5]
[Mary] [Week4][1]

 

[ltalbott]

Hi Vonnie

The records would be as follows:

Start Date End Date No of Days
Joe Bloggs 6/1/2009 11/1/2009 6
Joe Bloggs 12/1/2009 18/1/2009 7
Joe Bloggs 19/1/2009 19/1/2009 1

Not all cases in the raw data would produce more than one record, but there is a fundemental flaw with our absence reporting system as the end dates can't be amended from the weekly absence return, so additonal records have to be added.

What I would want to see from the above example is one record, i.e.

Joe Bloggs 6/1/2009 19/1/2009 14

This is because I have to determine how many actual cases there are and if it's long term (more than 20 days) or short term.

Does this help?

Thanks

 

[DavidBoyleHerts]

The only way to do it is to write some VBA code that goes through the table for each employee.

The algorithm is roughly as follows.

Select a distinct list of employees.

For each employee filter out their absences and sort them on start date in a recordset and set up an iterative loop.

Create a variable that is the sum of individual absence session to date and set it to zero.

Look at the first record and assign it to the length.
Assign the values in that record to variables
Look at the next record and compare its start date to the end date of the previous record in a function and if apropriate add it to the length of the current absence.

When you detect the end output the absence record. You can do this by inserting into a results table or saving in an output recordset to be used programattically.

Keep building up the value of continuing absences until you detect they ends then start up a new counting variable.

It is relative simple once you get used to DAO/ADO recordsets and looping code. This type of programming is quite common for access developeres prresented with theis type of issue. If you haven't coded like this before then it might be quite challenging but a valuable skill to pick up.

You need to learn to manipulate data in VBA or re-structure the underlying data.