[RESOLVED]Putting duplicated lines on one line

[Isengard]

Hello Ecniv,

The max will always be determined first manually and the destination table columns count will be adjusted according to it. I'll have a look at your suggestions to see which would be more suitable!

Thanks for the reply!

[Isengard]

After thinking about this thing for a while, I decided to go the ugly but functional way:

While Not rs1.EOF
    j = 1
    rs2.Open "SELECT * FROM TB_SOURCE WHERE champ1 = " & rs1!champ1 & "", con
    
    If rs2.RecordCount = 1 Then
        rs3.Open "INSERT INTO TB_DESTINATION(champ1, champ2, d1, d2, d3, e1, e2, e3, f1, f2, f3) VALUES(" & rs2!champ1 & ", " & rs2!champ2 & ", '" & rs2!d1 & "', '" & rs2!d2 & "', '" & rs2!d3 & "', '" & rs2!e1 & "', '" & rs2!e2 & "', '" & rs2!e3 & "', '" & rs2!f1 & "', '" & rs2!f2 & "', '" & rs2!f3 & "')", con
    ElseIf rs2.RecordCount > 1 Then
        While Not rs2.EOF
            If j = 1 Then
                rs3.Open "INSERT INTO TB_DESTINATION(champ1, champ2, d1, e1, f1) VALUES('" & rs2!champ1 & "', '" & rs2!champ2 & "', '" & rs2!d1 & "', '" & rs2!e1 & "', '" & rs2!f1 & "')", con
            ElseIf j > 1 Then
                If j = 2 Then
                    rs3.Open "UPDATE TB_DESTINATION SET d2 = '" & rs2!d1 & "', e2 = '" & rs2!e1 & "', f2 = '" & rs2!f1 & "' WHERE champ1 = " & rs2!champ1 & "", con
                End If

                If j = 3 Then
                    rs3.Open "UPDATE TB_DESTINATION SET d3 = '" & rs2!d1 & "', e3 = '" & rs2!e1 & "', f3 = '" & rs2!f1 & "' WHERE champ1 = " & rs2!champ1 & "", con
                End If

                If j = 4 Then
                    rs3.Open "UPDATE TB_DESTINATION SET d4= '" & rs2!d1 & "', e4 = '" & rs2!e1 & "', f4 = '" & rs2!f1 & "' WHERE champ1 = " & rs2!champ1 & "", con
                End If

                If j = 5 Then
                    rs3.Open "UPDATE TB_DESTINATION SET d5 = '" & rs2!d1 & "', e5 = '" & rs2!e1 & "', f5 = '" & rs2!f1 & "' WHERE champ1 = " & rs2!champ1 & "", con
                End If

                If j = 6 Then
                    rs3.Open "UPDATE TB_DESTINATION SET d6 = '" & rs2!d1 & "', e6 = '" & rs2!e1 & "', f6 = '" & rs2!f1 & "' WHERE champ1 = " & rs2!champ1 & "", con
                End If
            End If
            j = j + 1
            rs2.MoveNext
        Wend
    End If
    rs2.Close
    rs1.MoveNext
Wend

Since I will always know my max value, I will adjust the number of queries inside the loop accordingly. It's not the best programming but it works and that's what I wanted. If you think of a way to do all this dynamically, let me know!

Thanks