[RESOLVED]Putting duplicated lines on one line

Hello there, I'm trying to figure out how to bring each duplicated ID on the same line. What I'd like to do is put the data for each ID in an array then insert each array element into a table.

Code:

---

ID1        d1        d2        d3        e1        e2        e3        f1        f2        f3
1111        a                        1                        test1               
1111        b                        0                        test2               
2222        a                        1                        test1               
3333        a                        1                        test1               
3333        b                        1                        test2               
3333        c                        0                        test3               
4444        a                        0                        test1               
4444        b                        0                        test2

---

My problem is with the array part. How can I do it to have this result for example:

Code:

---

ID1        d1        d2        d3        e1        e2        e3        f1        f2        f3
1111        a        b                1        0                test1  test2       
3333        a        b        c        1        1        0      test1  test2        test3

---

Thanks in advance!

Is this Access or Excel?

ISegard - you can't at least not easily. What happens if there are more duplicates than 3 ?

One way would be to run through the lot counting dups and getting the max,
then looping through again to store into the positions.

Another alternative would be to use a pivot table.

Possibly a crostab(le)... read up and see which seems to do what you need.

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!

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

Code:

---

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