Counting and distributing absence days that span over months

[JackDani]

I have an absence database which records the first day ([startdate]) and the last day ([enddate]) of an absence. I am able to count the length of the absence in days between these 2 dates ([nofda]) , having holidays and weekends discounted so we are left with just the workdays value.

Now I have been asked to produce some graphs to show the total amount of absence days by month over any given time frame. Currently there's around 450 individual records present.

This is where my problem begins.

I already have form where we can set a range of parameters to filter records and then have a temp query created based on these criteria.

I have summed up the total days absent and grouped it by month using a sub query based upon this temp query and use this as the source for the graph.

The chart I created seemed fine at first glance, until I realised that the values shown on the graph do not 'split'. By that I mean if an absence started in January and ended in February for a total of 20 absence days, 10 of those days were in January and the other 10 in February.

This is highlighted even more when a member of staff has been off for several months. They went off sick in January and returned in September. The total number of absence days 180 (for example) is counted in it's entirety in the total for January and not spread over the months inbetween which of course skews the graph somewhat.

I have spent the last few days trying and failing to unpick this one. There is of course an enddate field which I clearly need to have included somewhere in these querys but I have not been able to get it right.

As this is the closest I have gotten, this is probably what I will have to run with and hope the boss doesn't drag me over the carpet!

++++++++++++++++++++++++++++++++++++++++++++++++++++++++
The SQL I am using to drag out the figures from the temp query is
'Qry name [QRY_Absence_Chart_By_Month]

SELECT Sum(qryTemp.NOfDA) AS SumOfNOfDA, Format(DateSerial(Year([startdate]),Month([startdate]),1),"mmmm yy") AS MonthYear
FROM qryTemp
GROUP BY Format(DateSerial(Year([startdate]),Month([startdate]),1),"mmmm yy")
HAVING (((Sum(qryTemp.NOfDA))>0))
ORDER BY Format(DateSerial(Year([startdate]),Month([startdate]),1),"mmmm yy");


The row source for the graph is:

SELECT [MonthYear],Sum([SumOfNOfDA]) AS [SumOfSumOfNOfDA] FROM [QRY_Absence_Chart_By_Month] GROUP BY [MonthYear];

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Any advice will be GREATLY appreciated.

Cheers,

Jack

 

[dhookom]

I would create a table of every day with a field that identifies if it is a working or non-working date.
[tt]
tblAllDates
================
TheDate date with every date you need
WorkOrNot integer 0 for work and 1 for not
[/tt]

Add this to a query with your absense table and don't join them. Set the criteria under the TheDate field to:

Between [StartDate] And [EndDate]

You can group this query by TheDate and Sum() the WorkOrNot field to find out the number of absenses on each date. If you group TheDate by Month, you will have monthly totals.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Ditto to Duane's suggestion.

IMHO, when you are dealing with issues like this...

1. the company calendar or working days ought to be an explicit rendering of the DATES to work, NOT a formula.

2. employees days worked also ought to be recorded as explicit DATES/HOURS worked, not a formula. Furthermore, the DATES/HOURS NOT worked, out also to be accounted for as, for instance...

sick, vacation, berevement, family leave, jury, unexcused, etc.



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MichaelRed]

Some homages de to Duans & Skip, they are, after all, reasonably correct in the Codd rules approach.

Alternatively. a bit of thinking suggests that the problem is potentially solvable with some logic:

Given a starting date and the number of WORK days the absence, one could just take the number of WORKING days from that date to the end of the month ans ascribe lessor of the number of days absent or the remaining working days of the month to that month, If any absence days remain, move to the following month ane repeat.

Of course, the similar approach could be used even if the number o absence days includes all contiugious days, simply be omittiing hte working days part.



MichaelRed