Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: How to Concatenate Rows from One Table

    Hello,
    I have seen some examples (How to concatenate multiple child records into a single value - Microsoft: Access Queries and JET SQL FAQ - Tek-Tips) of how to concatenate rows together but have yet to make it work.

    I have one table - a master email table - that houses all client ID's and email addresses. Example would be:

    ClientID - Email

    1 - email1@email.com
    1 - email2@email.com
    2 - email3@email.com
    3 - email4@email.com

    I need to concatenate these values based on clientID to send an email (versus sending the same email multiple times with one 'to' address.

    So ideally I would like to have something like this:
    for clientID = 1 would return email1@email.com;email2@email.com


    It seems like the attached would handle it but for some reason Access 2007 doesn't seem to recognize the concatenate() function.

    Any ideas on how to do this?

    Thanks.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    That link doesn't work for me. There are also

    Modules: Return a concatenated list of sub-record values

    Microsoft Access tips: Concatenate values from related records

    Both of which (and probably yours too) require the function to be in a standard module.
    Paul

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can adapt the following example. Replace the names of the table and of the columns in strSQL to adapt it to your database.
    [CODE]Function RowConcat(ID As Long) As String

    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim str As String

    strSQL = "SELECT Category FROM dbo_V_Order_Details " & _
    "WHERE PK_CF_Data = " & ID
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    With rst
    Do Until .EOF
    If Len(Nz(!Category, "")) > 0 Then
    If Len(str) > 0 Then str = str & ";"
    str = str & !Category
    End If
    .MoveNext
    Loop
    .Close
    End With
    Set rst = Nothing
    RowConcat = str

    End Function[/CODE
    Have a nice day!

  4. #4
    Join Date
    May 2014
    Posts
    1

    Smile Thank you!

    Worked great!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •