Average Caseload between Dates 1

[gRegulator]

Hi,

I have been using the following SQL code to find the average number of clients that are supervised over a selected time period. It works great. The date fields that it uses are [Probation Start] and [Probation Expiry].

PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime;
SELECT MALE, FEMALE, Sum(IIf([Probation Expiry]>[Forms]![frm_Table5_AverageCount]![txtEnd],[Forms]![frm_Table5_AverageCount]![txtEnd],[Probation Expiry])-IIf([Probation Start]<[Forms]![frm_Table5_AverageCount]![txtStart],[Forms]![frm_Table5_AverageCount]![txtStart],[Probation Start]))/([Forms]![frm_Table5_AverageCount]![txtEnd]-[Forms]![frm_Table5_AverageCount]![txtStart]) AS [Average Daily Count of Adult Offenders on Supervised Probation]
FROM tbl_offenders
WHERE (((tbl_offenders.[Probation Expiry])>=[Forms]![frm_Table5_AverageCount]![txtStart]) AND ((tbl_offenders.[Probation Start])<=[Forms]![frm_Table5_AverageCount]![txtEnd]))
GROUP BY MALE, FEMALE;

What I am looking to do is a similar objective, except this time I would like to calculate the number of people who have a Community Service requirement. The problem that I encounter is that the start and end dates for the Community Service may come from either [Probation Start] and [Probation End] OR [CS Start] and [CS End]. If the client has a community service requirement, then the check box [Community Service Order] in tbl_offenders will be checked as yes.

I hope this makes sense to everyone and I truly appreciate any help that you may have!

Thanks!

 

[TomCologne]

Assuming you want the CS date when

[Community Service Order]= True,

Probation date for all others.

You could use the Switch function like in the code applied to your "WHERE" string. Youll still need to modify the "SELECT" part.

WHERE (((Switch(tbl_offenders.[Community Service Order]=0, tbl_offenders.[Probation Expiry],tbl_offenders.[Community Service Order]=-1,tbl_offenders.[CS End])>=[Forms]![frm_Table5_AverageCount]![txtStart]) AND ((Switch(tbl_offenders.[Community Service Order]=0,tbl_offenders.[Probation Start],tbl_offenders.[Community Service Order]=-1,tbl_offenders.[CS Start])<=[Forms]![frm_Table5_AverageCount]![txtEnd]))
GROUP BY MALE, FEMALE;

Code's not tested.

Is this what you had in mind?


TomCologne

 

[gRegulator]

Tom,

That seems to be what I'm looking for. Right now I have the code written as:

PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime;
SELECT MALE, FEMALE, Sum(IIf([Probation Expiry]>[Forms]![frm_Table5_AverageCount]![txtEnd],[Forms]![frm_Table5_AverageCount]![txtEnd],[Probation Expiry])-IIf([Probation Start]<[Forms]![frm_Table5_AverageCount]![txtStart],[Forms]![frm_Table5_AverageCount]![txtStart],[Probation Start]))/([Forms]![frm_Table5_AverageCount]![txtEnd]-[Forms]![frm_Table5_AverageCount]![txtStart]) AS [Average Count of Offenders with CSW Requirement]
FROM tbl_offenders
WHERE (((Switch(tbl_offenders.[Community Service Order]=0, tbl_offenders.[Probation Expiry],tbl_offenders.[Community Service Order]=-1,tbl_offenders.[CSW Expiry])>=[Forms]![frm_Table5_AverageCount]![txtStart]) AND ((Switch(tbl_offenders.[Community Service Order]=0,tbl_offenders.[Probation Start],tbl_offenders.[Community Service Order]=-1,tbl_offenders.[CS Start])<=[Forms]![frm_Table5_AverageCount]![txtEnd]))))
GROUP BY MALE, FEMALE;

How would I modify the select statement? Right now when I run the query for a long set of dates (i.e. months between start and expiry) it gives me (what I believe are) accurate results. However, If i run it for only a few days, the results are inaccurate and sometimes even negative.

Thanks for all your help!

Greg

 

[TomCologne]

Greg,

You're welcome.

The parts of the SELECT statement referring to "[Probation Expiry],[Probation Start]" need to be switched, too.

Eg:
Sum(IIf([Probation Expiry]>[Forms]![frm_Table5_AverageCount]![txtEnd]...

Sum(IIf(Switch(tbl_offenders.[Community Service Order]=0, tbl_offenders.[Probation Expiry],tbl_offenders.[Community Service Order]=-1,tbl_offenders.[CSW Expiry])>=[Forms]![frm_Table5_AverageCount]![txtEnd]...


This is not exactly going to be a beautiful piece of code but it should work.


TomCologne

 

[TomCologne]

This should do it. Again, not a beauty:

PARAMETERS [Forms]![frm_Table5_AverageCount]![txtStart] DateTime, [Forms]![frm_Table5_AverageCount]![txtEnd] DateTime;
SELECT MALE, FEMALE, Sum(IIf(Switch([Community Service Order]=0, [Probation Expiry],[Community Service Order]=-1,[CSW Expiry])>[Forms]![frm_Table5_AverageCount]![txtEnd],[Forms]![frm_Table5_AverageCount]![txtEnd],Switch([Community Service Order]=0, [Probation Expiry],[Community Service Order]=-1,[CSW Expiry]))-IIf(Switch([Community Service Order]=0,[Probation Start],[Community Service Order]=-1,[CS Start])<[Forms]![frm_Table5_AverageCount]![txtStart],[Forms]![frm_Table5_AverageCount]![txtStart],Switch([Community Service Order]=0,[Probation Start],[Community Service Order]=-1,[CS Start])))/([Forms]![frm_Table5_AverageCount]![txtEnd]-[Forms]![frm_Table5_AverageCount]![txtStart]) AS [Average Count of Offenders with CSW Requirement]
FROM tbl_offenders
WHERE (((Switch([Community Service Order]=0, [Probation Expiry],[Community Service Order]=-1,[CSW Expiry])>=[Forms]![frm_Table5_AverageCount]![txtStart]) AND ((Switch([Community Service Order]=0,[Probation Start],[Community Service Order]=-1,[CS Start])<=[Forms]![frm_Table5_AverageCount]![txtEnd]))))
GROUP BY MALE, FEMALE;

Guys like PHV could probably put all of the above in one elegant line ;-)


TomCologne

 

[gRegulator]

WOW! That is exactly what I needed. Thanks for that last step. I am not very familiar with the switch statement, so that helped alot! Tom, Thank you so much.

Have a great day!

 

[TomCologne]

My pleasure & thanks for the star.

Maintenance tip:

Leave a comment in either the query or query fields' descriptions, or both!

It's easily skipped because everything is so present while you're working on the project.
But you don't want to come back after some time and go through a statement like this again to refresh your memory.

That is at least my experience.

You have a great day, too!


TomCologne