Concatenating many fields into one field

[olidav911]

Hi I have a problem where I want to concatenate many fields into one field, separated by a , or a ;

The table on the left shows the ID listed many times and it is only listed once on the right. In the names field on the right table I want the names from the left table listed to be separated by the , or ; based on the ID. The tables below are just an example of a much larger database.

[dcrake]

The VBA Version:

If you ar familiar with VBA then this is a method you can use

 
Dim Rs1 As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim IDPrevious As Long
Dim StrString As String
 
Set Rs1 = CurrentDb.OpenRecordset("Select * From OldTable Order By PK")
Set Rs2 = CurrentDb.OpenRecordset("NewTable")
 
If Not Rs1.EOF And Not Rs1.BOF Then
   IDPrevious = Rs1("PK")
   Do Until Rs1.EOF
       If Rs1("PK") = IDPrevious Then
          StrString = StrString & "," & Rs1("FieldName")
       Else
          Rs2.AddNew
          Rs2("PK") = IDPrevious
          Rs2("FieldName") = Mid(StrString,2)
          Rs2.Update
       End If
       Rs1.MoveNext
   Loop
   Rs1.Close
   Rs2.Close
End If
Set Rs1 = Nothing
Set Rs2 = Nothing

Above is aircode and untested. The logic is that is enumerates the old table in primarykey order concatenating the contents of the repeating field with a comma delimiter. Once the repeat ends it appends the PK and the created string to the new table.

David