If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rolling Average Grouping
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. |
#2
|
|||
|
|||
Rolling Average Grouping
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 |
Thread Tools | |
Display Modes | |
|
|