'Simple example 'idGroup,id2,id1 '6338546,14322882,13608969 '6338546,13608969,13255363 '6338546,6338546,14322882 '6338546,11837926,11316332 '6338546,12297571,11837926 '6338546,13255363,12811071 '6338546,12811071,12297571 '6338546,7610194,7343817 '6338546,7935943,7610194 '6338546,8531387,7935943 '6338546,6944491,6611041 '6338546,7343817,6944491 '6338546,9968746,9632204 '6338546,10381694,9968746 '6338546,6611041,0 '6338546,8920224,8531387 '6338546,9632204,8920224 '6338546,11316332,10941093 '6338546,10941093,10381694 Public Function GetidGroup() 'first id1 is always 0 sql = "SELECT idGroup, id2, id1 FROM TABLE_WITH_LINKED_LIST WHERE id1='0' ORDER BY id2 DESC" Dim rs As Recordset Dim uidLikedList As String, id2 As String, id1 As String Set rs = CurrentDb.OpenRecordset(sql) Dim total As Long Dim idGroup As String Dim incrément As Long, progress As Double total = rs.RecordCount incrément = 1 While Not rs.EOF progress = Math.Round(100 * incrément / total, 2) 'Print in order to avoir freezing Debug.Print progress If rs.Fields("idGroup") = "" Then id2 = rs.Fields("id2") idGroup = precedentUid(id2) rs.Edit rs.Fields("idGroup") = idGroup rs.Update End If incrément = incrément + 1 rs.MoveNext Wend rs.Close Set rs = Nothing GetidGroup = total End Function 'Recursive function 'Deepest so far is about 62 calls Public Function precedentUid(id2 As String) As String sql = "SELECT idGroup, id2 FROM TABLE_WITH_LINKED_LIST WHERE id1 = '" & id2 & "'" Dim rs As Recordset Dim precedentid2 As String Dim idGroup As String Dim ret As String Set rs = CurrentDb.OpenRecordset(sql) If rs.EOF Then rs.Close Set rs = Nothing precedentUid = id2 Else 'Some records have several references '56 impacted records : 'TODO : Give the min id2 to the group ret = "-1" While Not rs.EOF If rs.Fields("idGroup") = "" Then precedentid2 = rs.Fields("id2") idGroup = precedentUid(precedentid2) If ret = "-1" Or CLng(ret) > CLng(idGroup) Then ret = idGroup End If 'Debug.Print id2 & " " & precedentid2 & " " & idGroup rs.Edit rs.Fields("idGroup") = idGroup rs.Update End If rs.MoveNext Wend rs.Close Set rs = Nothing precedentUid = ret End If End Function
idGroup
MIN(id2)
idGroup="" And id1="0"
UPDATE LINKED_LISTS AS LL INNER JOIN ( SELECT MIN(id2) AS idGroup FROM LINKED_LISTS WHERE idGroup="" AND id1="0") AS T1 ON LL.id2 = T1.id2 SET idGroup = T1.idGroup
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)