Results 1 to 5 of 5

Concatenate values for multiple fields from related records

  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    83

    Concatenate values for multiple fields from related records

    Hi, this is directly related to allen brown's ConcatRelated function: www.allenbrowne.com/func-concat.html

    Consider this expanded example:

    Company | Order Dates | Invoiced Amounts
    --------------------------------------------------------------------------------------------
    Acme Corporation | 1/1/2007, 3/1/2007, 7/1/2000 | $300, $450, $600
    Wright Time Pty Ltd | 4/4/2007, 9/9/2007 | $150, $200



    I have a table that has a field, such as Company, where there are order dates and invoiced amounts (for that order date). I want to concatenate both of these fields respectively as in for each company: concatenate all order dates in one field and then concatenate all invoiced amounts in another field.

    Currently I am doing it with the following code:

    Code:
    SELECT CompanyName, 
    ConcatRelated("OrderDate", "tblOrders", "Company = " & """ Company & """") As [Order Dates], 
    ConcatRelated("InvoiceAmount", "tblOrders", "Company = " & """ Company & """") As [Invoiced Amounts] 
    FROM tblCompany;
    Concat Related looks like:
    Code:
    Public Function ConcatRelated(strField As String, _
        strTable As String, _
        Optional strWhere As String, _
        Optional strOrderBy As String, _
        Optional strSeparator = ", ") As Variant
    On Error GoTo Err_Handler
        'Purpose:   Generate a concatenated string of related records.
        'Return:    String variant, or Null if no matches.
        'Arguments: strField = name of field to get results from and concatenate.
        '           strTable = name of a table or query.
        '           strWhere = WHERE clause to choose the right values.
        '           strOrderBy = ORDER BY clause, for sorting the values.
        '           strSeparator = characters to use between the concatenated values.
        'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
        '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
        '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
        '           4. Returning more than 255 characters to a recordset triggers this Access bug:
        '               http://allenbrowne.com/bug-16.html
        Dim rs As DAO.Recordset         'Related records
        Dim rsMV As DAO.Recordset       'Multi-valued field recordset
        Dim strSql As String            'SQL statement
        Dim strOut As String            'Output string to concatenate to.
        Dim lngLen As Long              'Length of string.
        Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
        
        'Initialize to Null
        ConcatRelated = Null
        
        'Build SQL string, and get the records.
        strSql = "SELECT " & strField & " FROM " & strTable
        If strWhere <> vbNullString Then
            strSql = strSql & " WHERE " & strWhere
        End If
        If strOrderBy <> vbNullString Then
            strSql = strSql & " ORDER BY " & strOrderBy
        End If
        Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
        'Determine if the requested field is multi-valued (Type is above 100.)
        bIsMultiValue = (rs(0).Type > 100)
        
        'Loop through the matching records
        Do While Not rs.EOF
            If bIsMultiValue Then
                'For multi-valued field, loop through the values
                Set rsMV = rs(0).Value
                Do While Not rsMV.EOF
                    If Not IsNull(rsMV(0)) Then
                        strOut = strOut & rsMV(0) & strSeparator
                    End If
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                strOut = strOut & rs(0) & strSeparator
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelated = Left(strOut, lngLen)
        End If
    
    Exit_Handler:
        'Clean up
        Set rsMV = Nothing
        Set rs = Nothing
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
        Resume Exit_Handler
    End Function
    Is there a cleaner way of using or modifying this function in my queries which will look up all the concat-related fields (Order Dates and Invoiced Amounts in this example) where it be 2 fields as in this example, or 3, 4 or an even greater number? Any tips on increasing the speed of compilation for this function/query?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,578
    If the situation permits (matching data from records in the orders table), you could customize Allen's code. Basically make its SQL return multiple fields, create strings for each desired field within the loop, and return the concatenation of those strings. It wouldn't be as dynamic as Allen's, but you'd only be calling it once instead of once for each desired field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is online now Expert
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    856
    See if the JoinFromArray code from my site helps you:
    http://forestbyte.com/vba-code-samples/

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    accesstos's Avatar
    accesstos is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    273
    Also, search for the GetString() method of ADODB Recordset object. Maybe helps you.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,287
    The theDBGuy has a function SimpleCSV() that can concatenate field values. It could be used for multiple fields as per your example. BUT I don't think it would be easier or faster than what you have. It would call the function for each field in then for each record in the recordset/table.
    Function Source: http://accessmvp.com/thedbguy
    'v1.0 - 8/20/2013

    Table Animal
    AnimalId AName
    0 Geronimo
    1 Spot
    2 Jim
    3 Sam
    4 David
    5 BlueEyes
    6 Capitan
    7 Johnny

    Table AnimalLocs

    SightingDate GPSLong GPSLat AnimalId
    27-Mar-09 77.9 47.3 1
    28-Mar-09 77.34 46.8 1
    02-Dec-09 78.44 45.32 1
    13-Mar-09 76.33 48.9 2
    29-Mar-09 77.45 48.8 2
    21-Apr-10 78.53 47.54 2
    24-Aug-09 76.2 49.4 3
    21-Apr-10 78.23 47.52 4
    23-Apr-10 78.666 47.66 4
    14-Sep-10 77.7 49.3 5
    17-Sep-10 77.334456 48.9 5


    Sample Query
    Code:
    SELECT Animal.AName
    , simplecsv("select animalLocs.sightingdate from animalLocs
    where animalLocs.animalId =" & [animal.animalid]) AS sightedDates
    , simplecsv("select animalLocs.GPSLat   from animalLocs
    where animalLocs.animalId =" & [animal.animalid]) AS Locs_Lat
    , simplecsv("select animalLocs.GPSLong   from animalLocs
    where animalLocs.animalId =" & [animal.animalid]) AS Locs_Long
    FROM Animal;
    Query result

    AName sightedDates Locs_Lat Locs_Long
    Geronimo


    Spot 27-Mar-09,28-Mar-09,02-Dec-09 47.3,46.8,45.32 77.9,77.34,78.44
    Jim 13-Mar-09,29-Mar-09,21-Apr-10 48.9,48.8,47.54 76.33,77.45,78.53
    Sam 24-Aug-09 49.4 76.2
    David 21-Apr-10,23-Apr-10 47.52,47.66 78.23,78.666
    BlueEyes 14-Sep-10,17-Sep-10 49.3,48.9 77.7,77.334456
    Capitan


    Johnny



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

Similar Threads

  1. Replies: 6
    Last Post: 09-11-2017, 04:42 PM
  2. Concatenate values from related records
    By AccessDennis in forum Queries
    Replies: 11
    Last Post: 04-29-2016, 05:22 PM
  3. Replies: 11
    Last Post: 02-11-2015, 01:30 PM
  4. Replies: 5
    Last Post: 03-11-2013, 03:12 PM
  5. Replies: 1
    Last Post: 03-13-2012, 06:11 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
  •  
Tech Forums: Microsoft Office Forums