Access VBA loop to update column

Question

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.

could you have any transaction numbers that are more than 1 digit? E.g. CDY1053N10 — Matt Hall
– Matt Hall, Commented Nov 12, 2015 at 15:29
no, they will always be one digit, however, the length of the customer references could change as illustrated above. Also, if it helps, the customer reference will always end in a letter, therefore the transaction reference will always be letter, followed by one digit. — st87_top
– st87_top, Commented Nov 12, 2015 at 15:33
Have a look at Mid([Transaction_Ref],1,Len([Transaction_Ref])-1) — Fionnuala
– Fionnuala, Commented Nov 12, 2015 at 16:23

Matt Hall · Accepted Answer · 2015-11-12 17:07:30Z

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

Collectives™ on Stack Overflow

Access VBA loop to update column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related