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

    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
    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")
              Rs2("PK") = IDPrevious
              Rs2("FieldName") = Mid(StrString,2)
           End If
    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.


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