Sum data and update table

[WineSnob]

I have a table where I calculate monthly interest for x years. I would like the best way to sum the column Interest for each year (month 1-12, month 13-24, month 25-36 etc..) and update another table where interest is by the year?

Sum column interest where month = 1-12 and update table tblinterest where year = 1 and Bucket = 1
Sum column interest where month = 13-24 and update table tblinterest where year = 2 and Bucket = 1
Sum column interest where month = 25-36 and update table tblinterest where year = 3 and Bucket = 1

etc.... til x years.

 

[nschroeder]

I have not tested this, and my code has never been known to work the first time , but this should be a good start. It's the Click event of a button named Sum.

Private Sub cbSum_Click()
Dim db As Database, rsMo As Recordset, rsYr As Recordset, i As Integer

    Set db = CurrentDb()
    i = 1
    For i = 1 To 10 ' Adjust to desired number of years
        Set rsMo = db.OpenRecordset("SELECT Sum(tblMonthly.Interest) AS SumInterest FROM tblMonthly" & _
            " WHERE tblMonthly.Month > " & i * 12 - 12 & _
            " AND tblMonthly.Month < " & i * 12 + 1 & ";")
        Set rsYr = db.OpenRecordset("UPDATE tblYearly SET tblYearly.Interest = " & rsMo!SumInterest & _
            " WHERE tblYearly.Year = " & i & ";")
    Next
    Set rsMo = Nothing
    Set rsYr = Nothing
    Set db = Nothing
End Sub

 

[WineSnob]

Thanks nschroeder. Your code didn't work as advertised. However, it did prompt me to do the loop. I was able to get it to work. So thanks for the mental direction.

 

[nschroeder]

Excellent! That was my hope Could you post the code that did work? It might help someone else in the future. Thanks.