Results 1 to 5 of 5
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Concatenate Multiple Rows into one field

    I've got a table with photo filenames with each sku having multiple photos. I need to collect all filenames into one field, per each sku. I don't know much about functions. I'm assuming it has something to do with the original function was expecting a numeric field for FAMID, where I'm now using a text value in [Sku]. If that's the case, what needs to change in the function?

    SELECT [Sku] & Chr(13) & Chr(10) & Concatenate("SELECT [SortKey] & '. ' & [FileNam]
    FROM PhotosHighRes
    WHERE [Sku] =" & [Sku] & " ORDER BY [SortKey]",Chr(13) & Chr(10)) AS Photos
    FROM PhotosHighRes;

    Function courtesy of Duane Hookom, 2003 below:

    Function Concatenate(pstrSQL As String, _
    Optional pstrDelim As String = "") _
    As String
    'Created by Duane Hookom, 2003
    'this code may be included in any application/mdb providing
    ' this statement is left intact
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    ' John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    ' WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '
    '======For DAO uncomment next 4 lines=======
    '====== comment out ADO below =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)
    '======For ADO uncomment next two lines=====
    '====== comment out DAO above ======


    Dim rs As New ADODB.Recordset
    rs.Open pstrSQL, CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
    If Not .EOF Then
    .MoveFirst
    Do While Not .EOF
    strConcat = strConcat & _
    .Fields(0) & pstrDelim
    .MoveNext
    Loop
    End If
    .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
    strConcat = Left(strConcat, _
    Len(strConcat) - Len(pstrDelim))
    End If
    Concatenate = strConcat
    End Function

    thanks!

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try (added single quotes around SKU):
    SELECT [Sku] & Chr(13) & Chr(10) & Concatenate("SELECT [SortKey] & '. ' & [FileNam]
    FROM PhotosHighRes
    WHERE [Sku] ='" & [Sku] & "' ORDER BY [SortKey]",Chr(13) & Chr(10)) AS Photos
    FROM PhotosHighRes;
    Cheers,
    Vlad


  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Also, if you use the ADO library, look for the GetString() method.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Here is Allen Browne's version http://allenbrowne.com/func-concat.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I thought I replied to this yesterday but now don't see the reply. Your edit was a success. Much appreciated!

    Thanks!

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

Similar Threads

  1. concatenate rows into one
    By PaulGoer in forum Access
    Replies: 7
    Last Post: 08-17-2018, 02:48 AM
  2. Concatenate Rows into one row
    By rkalapura in forum Programming
    Replies: 4
    Last Post: 04-23-2015, 06:52 PM
  3. merge or concatenate two rows in one
    By vojinb in forum Queries
    Replies: 7
    Last Post: 08-03-2011, 09:15 AM
  4. Replies: 6
    Last Post: 04-07-2011, 12:33 PM
  5. Concatenate Multiple Rows into One
    By Knarly555 in forum Queries
    Replies: 5
    Last Post: 11-12-2010, 06:51 PM

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