Updating every records value for a field. (1 Viewer)

[MooTube]

I am looking for a way to move monthly data between columns, as I am only required to store information for the past 5 months.

CARRY OVER - Month 1 - Month 2 - Month 3 - Month 4 - Month 5

when I press a button I would like for month 5's information to populate the month 4 column, month 4's information to populate the month 3 column etc. Month 1 should then be added to carry over.

currently I have been able to get some code that will perform this upon only the selected record, however I need to get this working for every record in the db:

    Forms![UpdatePaid]![DQSubform].Form![Carry Over] = Forms![UpdatePaid]![DQSubform].Form![Carry Over] + Forms![UpdatePaid]![DQSubform].Form![Month 1]
    Forms![UpdatePaid]![DQSubform].Form![Month 1] = Forms![UpdatePaid]![DQSubform].Form![Month 2]
    Forms![UpdatePaid]![DQSubform].Form![Month 2] = Forms![UpdatePaid]![DQSubform].Form![Month 3]
    Forms![UpdatePaid]![DQSubform].Form![Month 3] = Forms![UpdatePaid]![DQSubform].Form![Month 4]
    Forms![UpdatePaid]![DQSubform].Form![Month 4] = "0"

I am relatively new to access vba coding, having done a little bit last year where I recieved very useful help from this website. If anyone has any ideas how I would do this I would be greatly appreciative.

 

[Galaxiom]

You are using an incorrect data structure. The data should all be in the same field of the table.

Use a CrossTab query to display it in separate month columns.

Delete the old data when no longer required.

 

[MooTube]

I have since worked out how to complete this task, by using a loop to commit all changes, and then a pop up box to say how many changes were made:

Private Sub EOMbut_Click()

With [Forms]![EndOfMonth]![AffSub].[Form].RecordsetClone
    Do Until .EOF
        .Edit
        !CarryOver = (!CarryOver + !Month1)
        !Month1 = !Month2
        !Month2 = !Month3
        !Month3 = !Month4
        !Month4 = "0"
        .Update
        .MoveNext
    Loop
        MsgBox "A Total of " & .RecordCount & " Clubs were updated"
        .Requery
    End With
        
End Sub

This cycles through all records and completes the task I needed. If anybody needs to answer this question in the future.