Rolling Average Grouping

  • Thread starter Thread starter D Collins
  • Start date Start date
D

D Collins

Hello,

I have the following data that I wish to group on a report and find the
cummulative average.

Device # Date Trigger Amount
12 6/26/08 NO 100
12 6/27/08 NO 300
12 6/28/08 YES 200
Total 600

12 6/29/08 YES 125
Total 125

12 6/30/08 YES 400
Total 400

12 7/1/08 NO 50
12 7/2/08 YES 100
Total 150

Here's the situtation:

If the trigger is No then I would like to add the amounts up until the
trigger is yes, but also to include that first Yes. So, therefore, there
might be additional groupings down the road when it hits another no--starting
the process again.

Thanks,
D.
 
Try this code. This will insert the records to a different table (Roll_rep)
in the format that you require. From your report, read from this table.

Sub ex()


Dim myConn As ADODB.Connection
Set myConn = CurrentProject.Connection
Dim MyRecSet As New ADODB.Recordset
MyRecSet.ActiveConnection = myConn
MyRecSet.Open "RollingAvg", , adOpenStatic, adLockOptimistic
MyRecSet.MoveFirst

Dim trigger, prevTrigger, amt As Integer, sumamt As Integer
Dim device As String, dt As Date, Orderid As Integer
Dim mySQL As String
Orderid = 1

While Not MyRecSet.EOF

device = MyRecSet.Fields("Device").Value
dt = MyRecSet.Fields("Date").Value
trigger = MyRecSet.Fields("Trigger").Value
amt = MyRecSet.Fields("Amount").Value

mySQL = "insert into Roll_rep values (" & Orderid & ",'" & device &
"',#" & dt & "#," & trigger & "," & amt & ")"
DoCmd.RunSQL mySQL

If trigger = False Then
sumamt = sumamt + amt
Else
sumamt = sumamt + amt
Debug.Print "Device " & device & ", " & sumamt
mySQL = "insert into Roll_rep (OrderId, Amount) values ( " & Orderid
& "," & sumamt & ")"
DoCmd.RunSQL mySQL
sumamt = 0
End If
Orderid = Orderid + 1


MyRecSet.MoveNext

Wend

MyRecSet.Close
Set MyRecSet = Nothing
Set myConn = Nothing

End Sub
 
Back
Top