from colomns to lines?

[Corwyn]

How to transform a table containg multiple records with a same name into a table with one record with a lot of field
example:
1|a
1|b
1|c
2|d
2|e
...
Become
1|a|b|c
2|d|e
...

 

[kjv1611]

Well, first of all, how many records? more than 65,000?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

If less than 65k lines, then you could just use the Transpose() formula in Excel. If greater than 65k, then maybe use a crosstab query in Access (if it will work with your data), or you could code it with SQL and/or using recordsets. You could look at one thread of mine which is a bit more complicated (I think from your example) than what you have here, b/c you are simply matching up values based on how they are matched up in original table..

take a look at thread705-902372

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Zion7]

KjV, that is quite the code & very effective i'm sure.

im going to try and offer a more primitive one, just to give corwyn an outline, (maybe not as daunting), of what's required.

but even this, may be in need of streamlining...(please KJV, any advice would be welcome, on any level).

Dim rec1, rec2, rec3 As ADODB.Recordset, x As integer

Set rec1 = New ADODB.Recordset: Set rec2 = New ADODB.Recordset
Set rec3 = New ADODB.Recordset

rec1.Open "SELECT DISTINCT field1 FROM tblTable1", CurrentProject.Connection, acOpenForward, acLockPessimistic

rec2.Open "NewTable", CurrentProject.Connection, acOpenDynamic, acLockOptimistic

Do Until rec1.EOF

rec2.AddNew
rec2!FirstField = rec1!Field1

rec3.Open "SELECT * FROM tblTable1 WHERE Field1 = " & rec1!Field1", CurrentProject.Connection, acOpenForward, acLockPessimistic

Do Until rec3.EOF
x = x + 1
rec2("txtField" & x) = rec3!Field1
rec3.MoveNext
Loop
x = 0: rec3.Close

rec2.Update

rec1.MoveFirst
Loop

rec1.Close: rec2.Close
set rec1 = Nothing: Set rec2 = Nothing: set rec3 = Nothing

Rough idea, not tested (which I'm a little wary of, in this case).

...couldn't resist to try...good Luck either way.

if all else fails, KJV's looks fullproof!