Combine Average figures for Months(rolling totals)

[WingandaPrayer]

Hello,

I think I have given a reasonable(fairly) explanation this time.

Combining and Averaging Monthly figures.

How to create a function or use a query ??

Each month per year in my query has an average number of days for work completed.

The third column is shown just for my example Number of days divided by number of jobs.

April 2000……..14.00…….'70/5
May2000……….24.33…….'365/15
June2000……….34.00…….'816/24
July2000………..39.25…….'1099/28
August2000…….54.38……'1414/26
September2000…54.41.…'2231/41
October2000……40.15……'1566/39

How would I group these together thus.
And show as

April2000 to April2000……….14.00….'70/5
April2000 to May2000………...21.75…'435/20
April2000 to June2000………..28.43….'1251/44
April2000 to July2000………...32.63….'2350/72
April2000 to August2000……..38.40….'3764/98
April2000 to September2000…43.12…'5995/139
April2000 to October2000……42.47….'7561/178

I can create a query April2000 to May 2000 and so on but then only write the answer down and enter the next set of dates and do the same.

I tried creating queries for each month and then linking….didn't work.

I tried creating queries, grouping the months and then linking….didn't work.(month 4 & 5 wouldn't combine?)

I tried writing my own function(ha ha!) having seen another unrelated question using


DatePart then Format function to get the date and then

Using Select Case etc with

Case Is >= #4/1/2000# <= #4/30/2000#
MonthlyGroupings = &quot;April 2000 - April 2000&quot;
Case Is >= #4/1/2000# <= #5/31/2000#
MonthlyGroupings = &quot;April 2000 - May 2000&quot;
Etc etc etc

The query wouldn't go past the first month grouping.
Then I realised the date entered would only be counted once which isn't what I want.

Any help appreciated.

Should I continue with the individual queries,group the queries(eg April2000 to June2000 etc).If so I'm I creating these correctly.

Hoping someone is still awake and able to understand !!!!!!!

David

 

[braindead2]

use this on the database where you refered to

&quot;The table(Lettings) has handout and handback as the fields both with the input mask (short date)&quot;

paste in debug and hit enter

? DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot

if successful then

? DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;,&quot;[handback]< #6/1/2001#&quot

if successful then

? DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;,&quot;[handback]< #6/1/2001#&quot
if successful then

?DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;,&quot;[handback] < #&quot; & dateadd(&quot;m&quot;,-3,date()) &&quot;#&quot

if each of those give a return
then on a forms button event

Dim x As Integer, avgint As Integer
Dim retstr As String

For x = 1 To 6
avgint = Nz(DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;, &quot;handback < #&quot; & DateAdd(&quot;m&quot;, x, #1/1/01#) & &quot;#&quot)
retstr = retstr & Format(#1/4/01#, &quot;MMMMYYYY&quot & &quot; to &quot; & DateAdd(&quot;m&quot;, x, #1/1/01#) & &quot; &quot; & avgint & vbCrLf
Next x
MsgBox retstr

If that don't do it then I give up!!!

 

[WingandaPrayer]

It's the pain in the **** back again.

Did what you said.

? DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot
37.7894736842105

? DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;,&quot;[handback]< #6/1/2001#&quot
22.125

?DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;,&quot;[handback] < #&quot; & dateadd(&quot;m&quot;,-3,date()) &&quot;#&quot
37.7894736842105


Private Sub Command1_Click()
Dim x As Integer, avgint As Integer
Dim retstr As String

For x = 1 To 6
avgint = Nz(DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;, &quot;handback < #&quot; & DateAdd(&quot;m&quot;, x, #1/1/01#) & &quot;#&quot)
retstr = retstr & Format(#1/4/01#, &quot;MMMMYYYY&quot & &quot; to &quot; & DateAdd(&quot;m&quot;, x, #1/1/01#) & &quot; &quot; & avgint & vbCrLf
Next x
MsgBox retstr
End Sub
January2001 to 1/1/2001 0
January2001 to 1/2/2001 0
January2001 to 1/3/2001 0
January2001 to 1/4/2001 0
January2001 to 1/5/2001 0
January2001 to 1/6/2001 0
January2001 to 1/7/2001 0

DateAdd(&quot;m&quot;, x, #1/1/01#) I did change this date to 12/31/00 to return the end of each month...still zero's

Is this what you expected ??

May be it is time to give up.You've already spent to much time trying to sort me out.


Thank you

David

p.s. I changed For x = 1 To 12.... January2001 to 01/01/02 returned 38

 

[braindead2]

David,
This is enough to make this old mans head hurt!
You got a return of 22.125 on data when we asked for records with dates less then 6/1/2001. You got a return of 37.789.. as the average for all records and the same average when we asked for records less then 3/27/2001&quot;
and yet a return of zero when we used the date criteria

If you want to try this on the same table this is going to count backwards from todays date. It should work as it is the same command you issued from the debug window.

Dim x As Integer, avgint As Integer
Dim retstr As String

For x = -12 To 0
avgint = Nz(DAvg(&quot;[handback]-[handout]&quot;, &quot;lettings&quot;, &quot;handback < #&quot; & DateAdd(&quot;m&quot;, x, Date) & &quot;#&quot)
retstr = retstr & DateAdd(&quot;m&quot;, x, Date) & &quot; &quot; & avgint & vbCrLf
Next x
MsgBox retstr

 

[WingandaPrayer]

No need for the tablets.I fiddled about at home(as you do) with the sets of dates.It worked.

I do have a final question(if allowed)??

How do I show decimal places.So far only whole numbers are shown(rounded).

Thanks again,

David.

loads of stars,if you were registered.

 

[braindead2]

instead of dim var as integer dim it as double

 

[WingandaPrayer]

Dumbo here

Is that

Dim x As Integer, avgint As Double


David

 

[braindead2]

yep

 

[WingandaPrayer]

Wow !!

The first thing i've managed to get right.

Thanks for all your time.

David



Don't shudder next time I ask a question.
Maybe i'll change my handle ;-)

I'm gone