Concatenate Column Values from Multiple Rows into a Single Column

[balllian]

I have picked up the following code for the above but i need to change this code so i can change about 90000 records. What is the best way to do this as im not the greatest with code etc.


Option Compare Database
Option Explicit

'> Hello,
'> I would Combining values from fields of more than 1 record in 1 field.
'> For example
'>
'> Name firstname number
'>
'> Heide Marcel 1
'> Heide Marcel 2
'> Heide Marcel 3
'> Kooring Yvonne 7
'> Kooring Yvonne 14
'>
'> Number is a unique number
'>
'> Result should look like
'> Heide Marcel 1,2,3
'> Kooring Yvonne 7,14
'>

' The only way I know to do this is through code. The attached VBA routine
' creates a table named "tblOriginal" and populates it with data (like above).
' Then it makes a copy of that table and aggregates the "number" field for
' matching "names".
'
' Works in A2k if you set a reference to Microsoft DAO Library.


Public Function FixTable() As Boolean
On Error Resume Next

Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String
Dim strColumn1 As String, strColumn2 As String

Set db = CurrentDb()
Call RecreateTables(db)

Stop
sSQL = "SELECT Column1, Column2 FROM tblOriginal " _
& "ORDER BY Column1, Column2 ASC"
Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)

If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
strColumn1 = rst!Column1
strColumn2 = rst!Column2

rst.MoveNext
Do Until rst.EOF
If strColumn1 = rst!Column1 Then
strColumn2 = strColumn2 & ", " & rst!Column2
Else
sSQL = "INSERT INTO tblCopy (Column1, Column2) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
db.Execute sSQL
strColumn1 = rst!Column1
strColumn2 = rst!Column2
End If
rst.MoveNext
Loop

' Insert Last Record
sSQL = "INSERT INTO tblCopy (Column1, Column2) " _
& "VALUES('" & strColumn1 & "','" & strColumn2 & "')"
db.Execute sSQL
End If

Set rst = Nothing
Set db = Nothing

DoCmd.OpenForm "frmOutput"

End Function

Private Function RecreateTables(ByRef dbs As DAO.Database)
On Error Resume Next
Dim sSQL As String

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblOriginal'") = 1 Then
DoCmd.DeleteObject acTable, "tblOriginal"
End If

sSQL = "CREATE TABLE tblOriginal (Column1 Text(10), Column2 Text(10))"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','1')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('A','2')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','1')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','2')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('B','3')"
dbs.Execute sSQL

sSQL = "INSERT INTO tblOriginal (Column1, Column2) VALUES ('C','1')"
dbs.Execute sSQL

' Delete Table, if exists
If DCount("*", "MsysObjects", "[Name]='tblCopy'") = 1 Then
DoCmd.DeleteObject acTable, "tblCopy"
End If

' Create Temp Table
sSQL = "SELECT Column1, Column2 INTO tblCopy " _
& "FROM tblOriginal WHERE 1 = 0;"
dbs.Execute sSQL

End Function

Any help would be much appreciated.

This is the format of my data

PRODUCTNO REMARKID REMARK
2954 11427 Fourteen bedroomed hotel situated by the old Penhelig harbour with views across
2954 11428 the estuary to the mountains beyond. Recommended by all leading hotels for quali
2954 11429 "ty food, wine and traditional ales

 

[Crowley16]

have you looked into a crosstab query?

--------------------
Procrastinate Now!

 

[balllian]

Im getting the wizard coming back and saying there is 'Too many Crosstab Column Headers' for me to do it this way.

 

[Crowley16]

combine firstname and lastname into 1 column and base the crosstab on this

--------------------
Procrastinate Now!

 

[balllian]

all ive got in my table are the following column headers. This is an example of one of the products.

PRODUCTNO REMARKID REMARK

2954 11427 Fourteen bedroomed hotel situated by the old Penhelig harbour with views across
2954 11428 the estuary to the mountains beyond. Recommended by all leading hotels for quali
2954 11429 "ty food, wine and traditional ales

I have approx 90000 rows of data similar to this for different productno's of which i need to merge.