Field Merge Solution

[merciless32]

If you have a table with data that looks like this:

ID Data
A111 D1
A111 D2
A111 D3

But you want to combine the data to show:

ID Data
A111 D1|D2|D3...

Here is the script! This is worth it's weight in gold!!! First, create an empty copy of the table that you want to convert.

Here's the script with notes. Just copy and paste it and make your changes!
---------

'Make sure to check your table's field variables for Zero length rules, primary keys, data type, etc. This will cause a lot of headaches otherwise.

Function Update()
On Error GoTo ErrorHandler
'Set database, recordsets, and strings. You can use these names or modify them so that you will better understand them. Just make them relevant to what you are doing.
Dim dbs As Database
Dim rsOrigin, rsDestination As Recordset
Dim strFinalData, strData, strSelect As String
Dim strID, strFinalID As String
Dim bSuccess As Boolean

'Sets tables and variables. Create 2 tables, one for the original data, (Origin_Table) and one for the combined data, (Destination_Table). Change these table to reflect yours.
Set dbs = CurrentDb
strSelect = "SELECT * FROM Origin_Table ORDER BY ID;"
Set rsOrigin = dbs.OpenRecordset(strSelect)
Set rsDestination = CurrentDb.OpenRecordset("Destination_Table&quot

'In my orignal data table there are 2 fields, "ID", and "Data".
strID = rsOrigin.Fields("ID&quot
strData = rsOrigin.Fields("Data&quot
strFinalData = rsOrigin.Fields("Data&quot

If Not rsOrigin.EOF() Then
rsOrigin.MoveNext
End If

'Conversion Actions
Do While Not rsOrigin.EOF()

If strObserver = rsOrigin.Fields("ID&quot Then

'This is where the field merge takes place. Just replace the "|" with whatever character you want to display
strData = rsOrigin.Fields("Data&quot
strFinalID = rsOrigin.Fields("ID&quot
strFinalData = strFinalData & "|" & strData

Else
'This adds records to Destination Table. There are two fields in this table called "User_ID" and "Questions".
rsDestination.AddNew
rsDestination.Fields("User_ID&quot = strFinalID
rsDestination.Fields("Questions&quot = strFinalData
rsDestination.Update
strID = rsOrigin.Fields("ID&quot
strData = rsOrigin.Fields("Data&quot
strFinalID = rsOrigin.Fields("ID&quot
strFinalData = rsOrigin.Fields("Data&quot
End If

rsOrigin.MoveNext

Loop

bSuccess = True
'Final actions and messages. Use whatever message you want in place of this one.
If bSuccess = True Then
MsgBox "All Records Updated Sucessfully!"
End If
Exit Function

'Error message
ErrorHandler:
MsgBox "Unhandled error #" + CStr(Err.Number) + "-" + Err.Description
End Function

-------------
Enjoy!!!