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]

Not to sure if this works correctly but did you try:

Expr1: Format(#4/1/2001#,&quot;mmmmyyyy&quot & &quot; to &quot; & Format(DateAdd(&quot;m&quot;,1,[yourDatefield]),&quot;mmmmyyyy&quot

 

[WingandaPrayer]

Okay,

Thanks for the response.

That pushes me part of the way but........

Expr1: Format(#1/4/2000#,&quot;mmmmyyyy&quot & &quot; to &quot; & Format(DateAdd(&quot;m&quot;,0,[yourDatefield]),&quot;mmmmyyyy&quot

produces this

April2000 to April2000……….14.00….
April2000 to May2000………...24.33…
April2000 to June2000………..34.00…
April2000 to July2000………...39.25…
April2000 to August2000…….54.38…
April2000 to September2000…54.41.
April2000 to October2000……40.15….

April2000 to May2000......only counts May.

I would like to see this(if possible)

April2000 to April2000……….14.00….
April2000 to May2000………...21.75…
April2000 to June2000………..28.43….
April2000 to July2000………...32.63….
April2000 to August2000……..38.40….
April2000 to September2000…43.12…
April2000 to October2000……42.47….

This result should be

April2000 to April2000…..... only counts April
April2000 to May2000........ should count April to May
April2000 to June2000….... should count April to June

......and on the the final month/year in the calculation

Any more ideas please.

Thank you

David

 

[braindead2]

How about something like this

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

For x = 1 To 5 'for 5 months
avgint = DAvg(&quot;[yourfield]&quot;, &quot;yourtbl&quot;, &quot;[yourtbl].[datefield] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/2001#) & &quot;#&quot
retstr = retstr & Format(#4/1/2001#, &quot;MMMMYYYY&quot & &quot; to &quot; & DateAdd(&quot;m&quot;, x, #4/1/2001#) & &quot; &quot; & avgint & vbCrLf
Next x
Debug.Print retstr
End Sub

 

[WingandaPrayer]

Sorry for the delay in repling to your help.
Works Server down ,up then down again.
Had to wait until I went home.Then I didn't have access to database.
Back to work,server still down…..back home etc etc


Sorry I'm really really Braindead

??Dumb question. Where does this go….you tested on a form/report

avgint = DAvg(&quot;[yourfield]&quot;, &quot;yourtbl&quot;, &quot;[yourtbl].[datefield] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/2001#) & &quot;#&quot

I'm not sure about the following.

The table with the information is LetttingsHandover
In this, one datefield is called DateOfHandover…..one called DateOfHandback
DateOfHandback doesn't always have a date in it for obvious reasons.A query shows the difference in days on the completed ones(Is not null on DateOfHandback)


What should I place in [yourfield] and [datefield]

avgint = DAvg(&quot;[yourfield]&quot;, &quot;LettingsHandover&quot;, &quot;[LettingsHandover].[datefield] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/2001#) & &quot;#&quot



Thanks for your continued help.

David

 

[braindead2]

I Did this on a form the button was the only thing on the form to launch the code. I had it print to debug window but you could easily make it go to a textbox IE..me!text1 = retstr
lets assume your query is named Avgdays
fields are

DateOfHandover DateOfHandback numberdays

the line in the code above should read
= DAvg(&quot;[numberdays]&quot;, &quot;avgdays&quot;, &quot;[avgdays].[dateofhandback] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/2001#) & &quot;#&quot
the loop is essential
look at the Davg function
what we are saying in the code is
give me the average [numberdays] in avgdays query for all records between 4/1/2001 and 5/1/2001
next loop
give me the average [numberdays] in avgdays query for all records between 4/1/2001 and 6/1/2001
each time it loops x is increased by 1 thus we use dateadd and increase the months of the records it is looking at by 1
good luck!

 

[WingandaPrayer]

Thanks for the help.
Information gradually sinking in.

David

 

[MichaelRed]

Averages of averages are not available from your description.

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

If we look at the desired results (second list), we note, for example that the April to may desired result of 21.75 is obtained by combining the april figures (70/5) witht the may figures (365/15) to get 425/20). You state that the thrid column is shown &quot; ... just for my example ...&quot;, however the figures ARE NECESSARY to compute the desired value. IF you set up your series of queries to include these values as two additional columns and do a simple aggregate query, the quotient of the two sums will yield the desired answer for each interval.




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[WingandaPrayer]

Michael.

Thanks for your reply.

David

 

[WingandaPrayer]

Braindead2...if you're still about

I tried with a test database.

The error comes up.

Invalid use of null
Run time error 94
on this line.
avgint = DAvg(&quot;[numberdays]&quot;, &quot;Avgdays&quot;, &quot;[Avgdays].[DateofHandback] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/2001#) & &quot;#&quot

Thanks

David

 

[braindead2]

not sure why you would be getting that error. You might try to set criteria in your query to eliminate the null values but I don't think it should matter.
Here is what I did so you can compare it to what you are trying to accomplish

I created a table (pasted below)
handout handback
4/1/2001 4/3/2001
4/2/2001 4/6/2001
5/1/2001 5/9/2001
5/8/2001 5/14/2001
6/1/2001 6/23/2001
6/15/2001 6/23/2001
7/2/2001 7/4/2001
7/9/2001 7/13/2001
7/7/2001
6/3/2001
notice some dates left blank.
I created a query named avgdays (Pasted here)
handout handback numberdays
4/1/2001 4/3/2001 2
4/2/2001 4/6/2001 4
5/1/2001 5/9/2001 8
5/8/2001 5/14/2001 6
6/1/2001 6/23/2001 22
6/15/2001 6/23/2001 8
7/2/2001 7/4/2001 2
7/9/2001 7/13/2001 4
7/7/2001
6/3/2001

I Placed this code in a form on a button event
Private Sub Command0_Click()
Dim x As Integer, avgint As Integer
Dim retstr As String

For x = 1 To 5 'for 5 months
avgint = DAvg(&quot;[numberdays]&quot;, &quot;avgdays&quot;, &quot;[avgdays].[handback] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/01#) & &quot;#&quot
retstr = retstr & Format(#4/1/01#, &quot;MMMMYYYY&quot & &quot; to &quot; & DateAdd(&quot;m&quot;, x, #4/1/01#) & &quot; &quot; & avgint & vbCrLf
Next x
Debug.Print retstr
End Sub

this is my return (pasted from the debug window)
April2001 to 5/1/2001 3
April2001 to 6/1/2001 5
April2001 to 7/1/2001 8
April2001 to 8/1/2001 7
April2001 to 9/1/2001 7
Is this what you are trying to do or am I off base here?

 

[WingandaPrayer]

Not you off base,it's just me.

The query I used DateofHandover,DateofHandback,numberdays.

numberdays being DateofHandback minus DateofHandover
DateofHandback has is not null as criteria.Still error with no criteria.

Am I correct in thinking when writing the code, dates have to be in US format.

I promise to get this right.......


Thanks for your time.


David

 

[braindead2]

I was able to reproduce the error if I did not have dates for in april it gave the error. Make this change to the one line of the code

avgint = Nz(DAvg(&quot;[numberdays]&quot;, &quot;avgdays&quot;, &quot;[avgdays].[handback] between #4/1/2001# and # &quot; & DateAdd(&quot;m&quot;, x, #4/1/01#) & &quot;#&quot)

 

[WingandaPrayer]

Braindead2

I haven't resolved my promise yet

Created another test database with the Nz function.Now when I click on the form(Form1) nothing happens !!

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

The query(avgdays) has handout,handback,numberdays(using DateDiff).

I've tried in both formats US( month day year) and UK(day month year)and changed the dates entered, DAvg & DateAdd to suit.

Still not sure what I am doing wrong(although I know that it is me).

Thanks for your continued interest,support & help

David

 

[braindead2]

When you say click on the form I assume you mean a button on the form! And when you say nothing happens Are you viewing the debug window after you trigger the event to run the code. The code just prints to the debug window. To view the debug window hit the button to trigger the code then hit control-G.

 

[WingandaPrayer]

This is the button I have on Form1
With the control wizards off.

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

For x = 1 To 5
avgint = Nz(DAvg(&quot;[numberdays]&quot;, &quot;avgdays&quot;, &quot;[avgdays].[handback] between #1/4/2001# and #&quot; & DateAdd(&quot;m&quot;, x, #1/4/01#) & &quot;#&quot)
retstr = retstr & Format(#1/4/01#, &quot;MMMMYYYY&quot & &quot; to &quot; & DateAdd(&quot;m&quot;, x, #1/4/01#) & &quot;&quot; & avgint & vbCrLf
Next x
Debug.Print retstr
End Sub

I click on the button and it doesn't go anywhere,not to the debug window.
This time I have used UK format.

Sorry about this.

David

 

[WingandaPrayer]

If I remove the Nz function it returns to Error 94

Null is a Variant subtype used to indicate that a data item contains no valid data. This error has the following cause and solution:

· You are trying to obtain the value of a Variant variable or an expression that is Null. For example:

MyVar = Null
For Count = 1 To MyVar
. . .
Next Count

Make sure the variable contains a valid value.

For additional information, select the item in question and press F1.


David

 

[braindead2]

now I am really puzzled
I made a table with just 2 fields
handout handback
a query with 3 fields (avgdays)
handout, handback and numberdays: DateDiff(&quot;d&quot;,[handout],[handback])
I put no data at all in the tables and then I copied and pasted the code from your last post
when I click the button I get

January2001 to 2/4/20010
January2001 to 3/4/20010
January2001 to 4/4/20010
January2001 to 5/4/20010
January2001 to 6/4/20010
which is exactly what you would expect. Your code does not have a space between the &quot;&quot;s
so the average is 0
If anyone else is following this thread what kind of results are you getting?

 

[braindead2]

the reason for the nz function is if there is no data for the dates then it was returning a null value thus the reason for the error 94

all we are doing in the code is

DAvg function is to calculate the average of a set of values in a specified set of records. We are limiting the recordset down to a date range. We assign that returned value to the variable &quot;avgint&quot;
we then build a string to display it in the format you want by assigning it to &quot;retstr&quot;
we do this for each month you want to cover each time increasing the date range by 1 month (our for next loop)
we then display the string in the debug window
ot sure where the glitch could be this time. Perhaps play with the select criteria. Can you get any return with the davg function?

paste this in the debug window and hit return

? DAvg(&quot;[numberdays]&quot;, &quot;avgdays&quot

what do you get?

 

[WingandaPrayer]

? DAvg(&quot;[numberdays]&quot;, &quot;avgdays&quot
37.7894736842105

David.....it has to be me!!