Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86

    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
    21,883
    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 offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    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 Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    514
    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
    15,722
    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



  6. #6
    Raddle is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    17
    Hi

    This thread looks to be relevant to my challenge but not sure ... relying on you to tell me ..

    I have a table with our various system interfaces types as fields. Each row represents a different functional item from the overall spec.

    Where a functional uses any interface it has an 'x' in the field (it came from non-database folk in Excel originally)

    I would like to concatenate into a string, in a new field, (e.g. Interfaces_Used) a note of all the interfaces for a given functional item.

    In my head I have something along the lines of 'build into string if x in any of these fields' ...

    Result would be a new field labelled something like Interfaces Used, containing Strings like 'Uses SMS, FTP, Billing'.

    Is this possible? Is this what I'm being shown earlier in this thread? If it can be done where do I even assign the code to run? Expression in a query?

    Wow, even I hate this question's sloppiness. Hope someone can help ..

    Thanks!

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,045
    If your data is stored in this Excel structure this is an interesting question as instead of data you simply have a X in the field.

    I would suggest you Normalise this to allow you to then concatenate the results back from the normalised structure.
    This will allow you to.

    Slightly contradictorily, you may be able to use excel to transpose this data for you into a normalised structure using a power query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    Hi there Raddle,

    Please review the attached file for a custom concatenate function I wrote for your specific case. Have a look at the various ways to call it in Query1. You just need to import the module into your db and customize the call to the function using your own table\query\field names.

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

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    And here is the function in case you don't want to look at the sample:
    Code:
    Public Function fnConcatenateIfString(sObj As String, sObjName As String, sInclude As String, sIDType As String, sID As String, vID As Variant) As String
    'sObj: use Q if object is query, T for table,S for Select SQL defining a recordset
    'sobjName: name of table or query
    'sInclude: string on which inclusion is based; i.e. "X", "Yes"
    'sIDType: "T" for text, "N" for numeric
    'sID: name of the unique ID for the record
    'vID: value of unique ID
    'you might want to add error handling
    'Usage
    'ConcTable: fnConcatenateIfString("T","Table1","x","N","ID",[ID])
    'ConcQuery: fnConcatenateIfString("Q","Query1","x","N","ID",[ID])
    'ConcSelect: fnConcatenateIfString("S","SELECT ID,SMS,FTP,Billing FROM Table1","x","N","ID",[ID])
    
    
    Dim db As DAO.Database, fld As DAO.Field, tdf As DAO.TableDef, qdf As DAO.QueryDef, rst As DAO.Recordset, prm As Parameter
    Dim sConcatenate As String, sWhere As String
    
    
    
    
    fnConcatenateIfString = sConcatenate
    If sIDType = "T" Then
        sWhere = "[" & sID & "] = '" & vID & "'"
    Else
        sWhere = "[" & sID & "] = " & vID
    End If
    
    
    
    
    Set db = CurrentDb
    
    
    If sObj = "T" Then
    'table
        Set tdf = db.TableDefs(sObjName)
        Set rst = tdf.OpenRecordset(dbOpenSnapshot)
        rst.FindFirst sWhere
    ElseIf sObj = "Q" Then
    'query
        Set qdf = db.QueryDefs(sObjName)
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
        Set rst = qdf.OpenRecordset(dbOpenSnapshot)
        rst.FindFirst sWhere
    Else
    'Recordset
        Set rst = db.OpenRecordset(sObjName & " WHERE " & sWhere, dbOpenSnapshot)
    End If
       
        For Each fld In rst.Fields
            If Trim(fld.Value) = Trim(sInclude) Then
                sConcatenate = sConcatenate & "," & fld.Name
            End If
        Next fld
        
        Set fld = Nothing
        rst.Close
        Set rst = Nothing
        Set tdf = Nothing
        Set qdf = Nothing
    
    
    If Len(sConcatenate) > 0 Then fnConcatenateIfString = Mid(sConcatenate, 2)
    Set db = Nothing
    End Function
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  10. #10
    Raddle is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    17
    As usual ... great grea work everyone. Thank you so much. There is a good bit of code for me to get into there .. I hope I can take it .... sorry for delay .. seemed I can't even manage my Outlook rules let along concatenation strings.. will come back as soon as I can ..

  11. #11
    Raddle is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    17
    ok so this was as lot to get my head round but your example file has helped millions ...

    You have created a function which can be pointed at tables, or queries or recordsets

    You have put all of them in a query and as it is points at a table, the T column works but the others error, as the query is looking at a table

    So I can take this and remove the entries for queries or record sets and just use the bit for my table ... I think

    Is that in line with your intention?

    I would need to import your function into my database obvs ....

    This is amazing .. I mean it is doing exactly what I needed .. I just need to marshal it correctly ... thank you so much my man ... amazing

  12. #12
    Raddle is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    17
    sorry .. concselect *IS* working as the query is pulling recordset from the table ...

  13. #13
    Raddle is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    17
    OM goodness it is fantastic ... I just added a space between the concat'd answer field ...

    sConcatenate = sConcatenate & "," & " " & fld.Name

    Goodness ... could be a late night now ..

  14. #14
    Raddle is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2021
    Posts
    17
    what is this bit actually saying ?

    fnConcatenateIfString = sConcatenate
    If sIDType = "T" Then
    sWhere = "[" & sID & "] = '" & vID & "'"
    Else
    sWhere = "[" & sID & "] = " & vID
    End If

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,607
    If the unique ID field (its name being the sID argument) is T(ext) then wrap its value (vID) in single quotes; else (N for number) leave its value as is.
    See https://www.youtube.com/watch?v=c2_fEdFBj_Q for more info on wrapping various data types in VBA SQL statements.
    Code:
    sConcatenate = sConcatenate & ", " &  fld.Name 'sConcatenate = sConcatenate & "," & " " & fld.Name
    ........
    ........
    If Len(sConcatenate) > 0 Then fnConcatenateIfString = Mid(sConcatenate, 3)  'you need to modify this line as now you have a comma and a space so start from third character
    I tried to make the function as flexible as possible; to use the Q(uery) option make sure the query has all the fields needed (the ID one and all the ones for the concatenating. The same query can be restricted for example to only include certain records, making it faster than running the function on the entire table.

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

Page 1 of 2 12 LastLast
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
  •  
Other Forums: Microsoft Office Forums