Best 5 Day Average

[taw]

I've looked thru a lot of posts, but can't seem to find the solution. It seems like this should be something I could figure out, but so far have not.

I have a table that is showing a production number for each day. What I'm trying to show is the best 5 day average production over a period of time.

Thanks,
Tom

 

[WayneRyan]

Tom,

Make a query.

Select ID, TheDate, GetAverage(ID)  <-- Assumes your Primary Key is autonumber called ID
From   YourTable                    <-- Though, you could pass your date field
Order By GetAverage(ID) Desc

Then make a public function.

Public Function GetAverage(ID As Long) As Long
Dim rst As DAO.RecordSet
Dim lngTotalQuantity As Long

Set rst = CurrentDb.OpenRecordset("Select Top 5 TotalQuantity " & _
                                  "From   YourTable " & _
                                  "Where  ID >= " & ID & " " * _
                                  "Order By ID")
lngTotalQuantity = 0
While Not rst.EOF And Not rst.BOF
   lngTotalQuantity = lngTotalQuantity + rst!TotalQuantity
   rst.MoveNext
   Wend
GetAverage = lngTotalQuantity
rst.Close
Set rst = Nothing
End Function

hth,
Wayne

 

[EMP]

I have a table that is showing a production number for each day. What I'm trying to show is the best 5 day average production over a period of time.

The question is a little vague. Different understanding of it may lead to different solutions.

Do you mean you want the query results to show the top 5 records based on productions in a specified period time and at the same time show the average production of these 5 records?

^

 

[taw]

I asked the customer for a little clarification and I think that I will end up having to do it all with code. I thought maybe I was missing something with a query that I could do. What they are looking for is a best 5 day 'rolling' average. So, if we are looking at the production for February, we would need to compare the average of 2/1 thru 2/5, then 2/2 thru 2/6, then 2/3 thru 2/7, etc. In addition, they have days that there is no production, so those days would be thrown out.

Thanks for the help guys. I can handle the code. Sometimes I'm a little ignorant of what I can do without code and just wanted to make sure I wasn't re-inventing the wheel on something like I usually do.

Tom

 

[John_W]

I've used the function DAvg to calculate moving averages in a query. Needs a little care with specifying the parameters...