CODEWITHSUNDEEP
loopsms-accessvba
st87_top
st87_top

Reputation: 57

Access VBA loop to update column

I have an Access database with about 500,000 records. There is a specific column which has the transaction reference.

This is of the form:

Transaction_Ref
CDY1053N1
CDY1053N2
CDY1053N3
JFD215D1
JFD215D2

Where CDY1053N and JFD215D are customer references, and the 1,2,3, etc which follows is the transaction number.

What I am looking for is a loop which will update a column called "Group". This will go to row 1, and loop through the database to find transaction references similar to CDY1053N and assign a group ID, for example:

Transaction_Ref   Group_ID
CDY1053N1            1
CDY1053N2            1
CDY1053N3            1
JFD215D1             2
JFD215D2             2

Any ideas please?

Thanks for the help.

Upvotes: 0

Views: 2727

Answers (1)

Matt Hall
Matt Hall

Reputation: 2412

This might not be the best or most elegant way to do this (particularly with the number of records you have), but this worked on my small set of test records.

I've assumed Transaction_Ref and Group_ID are in the same table and I've called that table tblTransactions.

I've also assumed that you might want to run this on new data so have nulled the Group_ID before looping through and resetting the values. This could mean that a different value for Group_ID gets assigned for a group of records (for example, were your records change order between subsequent runs of this sub).

If that's a problem you'll need to tweak this a bit.

Public Sub AssignGroupID()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    Dim i As Integer

    Set db = CurrentDb

    ' Clear the Group_ID column (in case you want to run this more than once)
    sql = "UPDATE tblTransactions Set Group_ID = Null"
    db.Execute sql

    ' Open your table with the Transaction_Ref and Group_ID fields
    Set rs = db.OpenRecordset("tblTransactions")

    ' Zero the counter
    i = 0

    ' Start the loop (set it to end when it gets to the last record)
    Do While Not rs.EOF

        ' Only update Group_IDs that haven't got a value yet
        If IsNull(rs!Group_ID) Then

            ' Push the counter on
            i = i + 1

            ' Update all Group_IDs with current counter number that
            ' match the customer reference of the current record
            sql = "UPDATE tbltransactions Set Group_ID = " & i & " WHERE " _
                & "Left(tblTransactions.Transaction_Ref, Len(tblTransactions.Transaction_Ref) -1) = '" _
                & Left(rs!Transaction_Ref, Len(rs!Transaction_Ref) - 1) & "'"
            db.Execute sql

        End If

        ' Move to the next record
        rs.MoveNext

    Loop

    'clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Upvotes: 1

Related Questions