Year End Summary Query: Average of Each Month

[gRegulator]

Hi,

I am trying to make a year end summary query with the Average Caseload of clients supervised. So what I want is the average Caseload from January, February, March, etc. and then add them up and divide by 12, to get a yearly monthly average.
The fields I wish to have a count for are [Probation], [Conditional Sentence],[Fine Option Program] and [Community Service Order]. The fields all come from the table tbl_offenders.
I have no idea how to set this up. Does anyone have any suggestions?

Thanks in advance

 

[PHV]

You want to count WHAT where which CONDITION is true ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gRegulator]

I want to have a count of clients on [Probation], [Conditional Sentence],[Fine Option Program] and [Community Service Order]. (These are all check boxes).

I want this count to be an average for every month.

i.e.
January Probation - 36
Conditional Sentence - 12
Fine Option Payment - 2
Community Service Order - 24

So, its a count of clients under each disposition per month, for each month of the year...

Then add these up and divive by 12 to have the average monthly case load for the entire year.

I hope this clears things up a little.

Thanks!

 

[PHV]

Something like this ?
SELECT Format([date field],'yyyy-mm(mmmm)') AS Period
, Sum(IIf([Probation],0,1)) AS [# Probation]
, Sum(IIf([Conditional Sentence],0,1)) AS [# Conditional Sentence]
, Sum(IIf([Fine Option Payment],0,1)) AS [# Fine Option Payment]
, Sum(IIf([Community Service Order],0,1)) AS [# Community Service Order]
FROM tbl_offenders
GROUP BY Format([date field],'yyyy-mm(mmmm)')
UNION ALL SELECT Format([date field],'yyyy-\Year')
, Sum(IIf([Probation],0,1))/12
, Sum(IIf([Conditional Sentence],0,1))/12
, Sum(IIf([Fine Option Payment],0,1))/12
, Sum(IIf([Community Service Order],0,1))/12
FROM tbl_offenders
GROUP BY Format([date field],'yyyy-\Year')
ORDER BY 1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gRegulator]

I think thats on the right track...

Each case load per month should reflect whether the cases are active or not.

_______

SELECT 'Probation' AS ConditionDescription,Male, Female, [Probation Start] as [Start], [Probation Expiry] as [Expiry]
FROM tbl_Offenders WHERE Probation = True

UNION ALL SELECT 'Conditional Sentence',  Male, Female, [CS Start] as [Start], [CS Expiry] as [Expiry]
FROM tbl_Offenders WHERE [Conditional Sentence] = True

UNION ALL SELECT 'Fine Option Program', Male, Female, 
       NZ([Probation Start],[CS Start]) As [Start],
       NZ([Probation Expiry],[CS Expiry]) As [Expiry]
FROM tbl_Offenders WHERE [Fine Option Program] = True

UNION ALL SELECT 'Community Service', Male, Female, 
       NZ([Probation Start],[CS Start]) As [Start],
       NZ([Probation Expiry],[CS Expiry]) As [Expiry]
FROM tbl_Offenders WHERE [Community Service Order] = True;

So from this I want each case month to reflect whether the cases are active or not.

e.g. For active cases in January, i think it would be something like [Probation Expiry] occurs after January 1st and [Probation Start] occurs before January 31st. it doesnt matter if the file is active for the whole month, just if it is active at some time in the month.

So I would like the query, if possible, to find the averages from all of these 12 months over the year, and then do an overall yearly average.

It is not really important whether or not these values show up in the query, really I just need the overall average.

I would also like the user to be able to input which year, or dates that we can use this for. Most likely each year will be considered from April 1 to March 31 of the following year.