Query: Average Daily Counts per Month 2

[gRegulator]

Hi,

I am trying to figure out how to calculate an average daily count of clients that we supervise. I have all my data stored in a Master table called tbl_offenders.

From the tbl_offenders I would like the following fields:
Male, Female, Probation Start, Probation Expiry. Where Male and Female are yes/no fields and Probation Start and Expiry are Date Fields.

So: I would like the query to be able to basically populate the following table:

Male Female Total

April | | |
May | | |
June | | |

etc....

So, a client would only be counted if his/her probation started sometime in the month and expired sometime in a given month. I.e. If Client #123 started probation on the 26th of May, then he would be counted for may. However, i would like it to be a daily count, so i was thinking that maybe there is a way to give them a count for each day they are on probation. i.e. Client #123 would receive 6 'points' because for May he is under our supervision for 6 days. I would like the 'points' to be tallied and divided by the number of days in each month, to get an average DAILY count of the number of clients we supervise for that month.

Is this possible? I may be going about this the total wrong way, but hopefully it is clear what I am trying to accomplish.

Thanks in advance and I truly appreciate any help I can get!

 

[OnTheFly]

I can give you the ways to get the numbers you want and maybe that will be enough to get you started. It sounds like a very complicated query using several queries within the query to accomplish the results. If it was me I would be doing the calculting and gathering of information through code and put it into a temp table. Anyway to get the number of days used in the month you would use

Number of days on probation for the month:
=DateDiff("d",[Probation Start],DateSerial(Year([Probation Start]),Month([Probation Start])+1,0)+1

Number of Days in the month:
=Day(DateSerial(Year([txtDate]),Month([txtDate])+1,0))

Hope this helps.

OnTheFly

 

[gol4]

This will give you the average per day for a time period
may have to run it for each month datemin is start datemax is ending date

PARAMETERS datemin DateTime, datemax DateTime;
SELECT Sum(IIf([Probation Expiry]>[datemax],[datemax],[Probation Expiry])-IIf([Probation Start]<[datemin],[datemin],[Probation Start]))/([datemax]-[datemin]) AS Expr1
FROM tbl_offenders WHERE (((tbl_offenders.[Probation Expiry])>=[dateMin]) AND ((tbl_offenders.[Probation Start])<=[datemax]));

 

[gRegulator]

Hi,

Thank you both for your advice.

gol4, I used your suggestion and it gave me exactly what I was looking for!

I really appreciate the help, that was one of the last things I have to finish on my database, this brings me one step closer!

Thanks again and have a great day!

Greg

 

[gRegulator]

gol4,

That code you gave me works great, now i am just wondering if you can show me how to divide the data so it will break it down into male and female clients.

Male and Female are options from the same table (tbl_offenders) and are check boxes.

So instead of having a total average, i could have the average male clients and the average female clients for that time period.

I appreciate you help!

 

[gol4]

2 fields, male and female yes no fields????
1 field with a M or F maybe. Any way
since I assume they can't be both all you need to do is add the one field either male or female and a group by clause 1's will be male 0 will be female

PARAMETERS datemin DateTime, datemax DateTime;
SELECT MALE, Sum(IIf([Probation Expiry]>[datemax],[datemax],[Probation Expiry])-IIf([Probation Start]<[datemin],[datemin],[Probation Start]))/([datemax]-[datemin]) AS Expr1
FROM tbl_offenders WHERE (((tbl_offenders.[Probation Expiry])>=[dateMin]) AND ((tbl_offenders.[Probation Start])<=[datemax]))
GROUP BY MALE;

hope that helps you some.

 

[gRegulator]

gol4, thanks again, i managed to have both male and female in there just by adding the female field to your sql statement to read:

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;

So I got what I was looking for!

Thanks so much