Absences that span one month into another

[JackD]

Hi,

I am having difficulty showing absence days in a graph. The amount of absence days is correct, however the distribution of these days doesn't work out the way I would have liked.

If you look at the example graph on my attachement, one person had a very long absence which lasted for 599 work days. (This figure is after holidays and weekends have been taken out).

The first day of absence happened in September 2006 and ran through until the end of December 2008. As you can see, my chart has accredited the entire length of the absence to that 1 month. Ideally I would have liked those days to have been distributed among the months inbetween the startdate and the enddate so that when these charts are created they aren't skewed by absence instances like this one.

My initial reponse was to count unusually long absences like this one as a "long term" and then filter them out from the equation via the query so they are not counted at all. However this still leaves me with the problem of much shorter absences that originate in one month and end in another.

eq. an absence of 10 days has 4 absence days in July and the remaining 6 absence days August, the 10 days are represented as having occurred in July.

I have no idea how to resolve this particular conundrum, whether an additional query needs to be created on the back of the monitoring table or if my current query needs a complete makeover.

Also please note that the filter that has been applied on "qryTemp" is the result of a form where the user sets criteria so they can focus the report on specific areas. These filters can be discounted if need be.

Any assistance would be greatly apprecitated.

Thanks.

Edit: Remembered to attach the file this time....

 

[Rabbie]

No attachment found

 

[Rabbie]

One possible solution is to split absences into segments that match month boundaries. then you should get the correct results for each month.

 

[JohnLee]

Hi JackD,

The reason your chart has accredited to one specific month is because of how you have presented the information. the query has a start date that begins in one month of one year and ends in another month of another year. The query expression is looking for the start date of the month and year only and does not look for the end month and year. Therefore your query will always return data based on your start date, which in turn means your report will return just that, which happens to be September 2006 [and this is the case for all your other data too].

You would need to present your data in such a way that enables the sort of breakdown your looking for. The query has no way of knowing from the information presented how to break it down in the way you want.

I would suggest that you need to look at how you are getting your data in the first instance and work out how you need to receive it, so that you can present the data in the way needed to produce the sort of report your looking for.

John