Results 1 to 2 of 2
  1. #1
    olidav911 is offline Novice
    Windows XP Access 2000
    Join Date
    Jun 2009
    Posts
    2

    Concatenating many fields into one field

    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.



  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    The VBA Version:

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

    Code:
     
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Splitting a field to separate fields
    By Petefured in forum Programming
    Replies: 8
    Last Post: 06-08-2009, 04:11 AM
  2. Replies: 0
    Last Post: 06-03-2009, 10:25 PM
  3. Replies: 1
    Last Post: 08-04-2008, 03:30 AM
  4. Replies: 1
    Last Post: 10-26-2007, 07:29 AM
  5. Concatenate two fields (text & number) for key field
    By Larry Elfenbein in forum Forms
    Replies: 2
    Last Post: 11-10-2005, 07:45 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums