Graphs: Distributing absence days that span between months

[JackD]

I am developing 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.

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 but I have not been able to get it right.


++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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

 

[DCrake]

What type of chart are you hoping to create?

Have you thought about creating a crosstab query based on your underlying query that uses months as column headings. This would give you the total number of days that are accumilated by month. You could then use this query as the basis for your graph.

Create a mock graph in Excel and post the workbook to show intended output.

 

[JackD]

I have attached a pdf of the graph have managed to cobble together.
As you can see the data was only being input from around July 2008 onwards but it shows the problem I have.

The total for March shows there were 104 days lost to absence.

Actually, that absence began in March and ran through until August, lasting 104 days (weekends and any holidays inbetween have already been discounted at the data input stage via a module that calculates absence between the startdate and enddate)

What I would like to have is to see those 104 days distributed between the intervening months and shown in the graph.

Thanks,

Jack