[RESOLVED]Putting duplicated lines on one line

[Isengard]

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.

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:

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!

[salvelinus]

Is this Access or Excel?

[Ecniv]

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.

[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