Update field based on criteria from another record (row)

[IKMD66]

Hi,

Below is an example of the data I currently have.

I need to update the field ConcatenateGroup (when null) with the value from
the first record where OriginalID is less than the current record and GroupID
is not null

For example the records where OriginalID = 19 through 33 should be updated
with the ConcatenationGroup 17, whereas the record where OriginalID = 45
should be updated with the ConcatenationGroup 43....and so on

OriginalID GroupID ConcatenateGroup
17 10 17
19
21
23
25
27
29
31
33
43 36 43
45
191 184 191
193
203 196 203
205
247 240 247


Can anyone assist with the logic required to do this?

Thanks in advance.

Kirk

 

[Steve Sanford]

Hi Kirk,

Maybe this will get you started. Test this on a COPY of your database.

There is one place that you have to change because I don't know the name of
your table:

'******** change this to your table name
TableName = "YourTableNameHere"
'********

'---------------------------------------------
Public Sub UPDT_Group()
Dim d As DAO.Database
Dim r As DAO.Recordset
Dim sSQL As String
Dim cg As Long
Dim k As Long
Dim TableName As String

'******** change this to your table name
TableName = "YourTableName"
'********


sSQL = "SELECT OriginalID, ConcatenateGroup"
sSQL = sSQL & " FROM " & TableName
sSQL = sSQL & " ORDER BY OriginalID;"


Set d = CurrentDb

Set r = d.OpenRecordset(sSQL)
k = 0 'kounter - # of records updated
If Not r.BOF And Not r.EOF Then
r.MoveFirst

cg = r.Fields("ConcatenateGroup")
'first record must not be Null or empty
If Nz(cg, 0) > 0 Then
r.MoveNext
Do While Not r.EOF
'check field if NULL
If IsNull(r.Fields("ConcatenateGroup")) Then
' no value - update field
r.Edit
r.Fields("ConcatenateGroup") = cg
r.Update
k = k + 1
Else
'get new value
cg = r.Fields("ConcatenateGroup")
End If
r.MoveNext
Loop
Else
'error - first record NULL
MsgBox " First record cannot be NULL, empty or 0"
End If
Else
' error - No records
MsgBox "No records found"
End If

MsgBox "Done - " & k & " Records updated "

On Error Resume Next
r.Close
Set r = Nothing
Set d = Nothing

End Sub
'---------------------------------------------


HTH

 

[IKMD66]

Steve,

I expected a few pointers on what to do - not the complete code.

What can I say - I cut and paste the code, changed the table name and it
worked first time!

Seriously - Thank you for taking the time to do this.....I'm just a learner
in this arena and it would have taken me some time to get the answer on my
own.

Many thanks.

Kirk

 

[Steve Sanford]

Glad to be of help (and that it worked) . I've done something like this a few
times, so it was easy to throw it together.

But I cheated a little - no error handling, doesn't check to see if the
field "ConcatenateGroup" is a number or if it has a space or is a ZLS.

If this is for more than a one time, you might want to beef it up.