Join (concat) values from one field from a table or query

Gustav BrockMVP
CERTIFIED EXPERT
Published:
Updated:
Edited by: Andrew Leniart
As you can union records, you can join field values. Presented here, DJoin offers increased speed and flexibility compared to the ancient ConcatRelated and similar functions. Further, it offers better read-out of Multi-Value fields.

History


For decades, functions have been around to solve the simple task of joining (concatenating) the values from a single field of many records to a single field value - as illustrated by the title picture, where the values from two keys (left) are joined into one field for each key (right) with a delimiter (or separator), here a space.


In VBA, this can be done in a loop where you step through an ordered recordset and build the joined values but, for most practical purposes, you will prefer a query which will return the result as a recordset. Such a query can be used as a source for a form or a report.


This is done by calling a subquery for each key, thus it is mandatory for large sets of records that the field holding the key is indexed, or the query may run at an unacceptably slow speed.

Still, the speed of the query may be too slow for smooth scrolling of large tables.


Further, being limited to a table or a saved query as source represents a limitation, because if you need anything special, you will have to create a new saved query for this purpose only.

Finally, caching of the joined values is missing, which means that repeated calls will be nearly as slow as the first call.


Improvements


So, we have some areas for improvement and added flexibility:


  • Better speed, indeed when browsing
  • A wider choice of source types - like pure SQL
  • Caching of results for vastly improved speed for repeated calls


But first, the function to be discussed, DJoin, is named such to signal the familiarity with the native domain aggregate functions - DLookup, DCount, etc. - as it aggregates the values from one field from many records to one string - much like Join does for an array, for example:


' Create array.
EmailAddresses = Array("bell@example.com", "ann@example.com", "vicky@example.com", "kim@example.com")

' Join values of the items of the array.
AllEmailAddresses = Join(EmailAddresses, ";")

' View result.
Debug.Print AllEmailAddresses 
' -> bell@example.com;ann@example.com;vicky@example.com;kim@example.com

To create a similar output from a table, take the Employees table from the Northwind example database and extract the e-mail addresses with this query:


SELECT
    Employees.[First Name],
    Employees.[Job Title],
    Employees.[E-mail Address]
FROM
    Employees;

and save it as JobTitleEmailAddress. It will output:



Now, create another query:


SELECT 
    Employees.[Job Title], 
    DJoin("[E-mail Address]","[Employees]","[Job Title]='" & [Job Title] & "'",";") AS [E-mail Addresses]
FROM 
    Employees
GROUP BY 
    Employees.[Job Title];

and save it as JobTitleEmailAddresses. It will output:


You'll notice, that with the specified delimiter - a semicolon - it provides a valid multi-receiver e-mail address string.

In other words: The e-mail addresses have been joined.


Improve speed

To speed up repeated calls, you can use caching. You may ask why repeated calls will happen, and they may not for a single request having a small table as a source. But for large tables, the function may be called up to five times per key because of the query optimiser and the order of the records as read by Access which both are out of our control.


To avoid the extra cost (time) for a repeated call, the result for each key can be stored in a Collection

The function SpeedTest  (from the attached demo below) reveals - for a large table of ~170,000 records having 424 keys - a speed improvement on the first run of about 25%. Repeated calls run about 16 times faster - a dramatic speed increase:


Method
first call
Subsequent calls
ConcatRelated
2.15s
2.14s
DJoin
1.59s
0.09s


The example function ConcatRelated by Allan Browne can be found here. It is also included in the demo (see below).


SQL as source

Often you will not have the exact table or query to deliver the data to join. If so, you had to create and save a new query just for the purpose. With DJoin you can pass a normal Select query as the source which allows for all kinds of sorting, filtering, and grouping.

As an example, here is a query with a join, which will be used later:


SELECT TShirt.Id, Size.Code 
FROM TShirt 
LEFT JOIN [Size] ON TShirt.Sizes.Value = Size.Code 
ORDER BY Size.Id


Caching of results

As mentioned above, DJoin caches all the results to cut time spent on repeated calls. This has the effect, that if the query is used as the source for a form, browsing this will happen at nearly the same speed as if you browsed the clean table or query without a DJoin expression.

This improves the user experience and prevents the application from being experienced as "slow".

Two queries, Concat and Join, which you can open and browse, are included in the demo.


To clear the cache, simply call DJoin with no arguments:


DJoin

This could be done in the OnClose event of the form or report.


DJoin - the function


To handle Multi-Value fields in an efficient matter and to avoid duplicated code, DJoin uses a helper function that will call itself by recursion when the field to join is a Multi-Value field:


' To be called from DJoin.
'
' Joins the content of the first field of a recordset to one string
' with a space as delimiter or an optional delimiter, returned by
' reference in parameter Result.
'
' 2019-06-11, Cactus Data ApS, Gustav Brock
'
Private Sub CollectValues( _
    ByRef Records As DAO.Recordset, _
    ByVal Delimiter As String, _
    ByRef Result As Variant)
    
    Dim SubRecords  As DAO.Recordset
    
    Dim Value       As Variant

    If Records.RecordCount > 0 Then
        While Not Records.EOF
            Value = Records.Fields(0).Value
            If Records.Fields(0).IsComplex Then
                ' Multi-value field (or attachment field).
                Set SubRecords = Records.Fields(0).Value
                CollectValues SubRecords, Delimiter, Result
            ElseIf Nz(Value) = "" Then
                ' Ignore Null values and zero-length strings.
            ElseIf IsEmpty(Result) Then
                ' First value found.
                Result = Value
            Else
                ' Join subsequent values.
                Result = Result & Delimiter & Value
            End If
            Records.MoveNext
        Wend
    Else
        ' No records found with the current criteria.
        Result = Null
    End If
    Records.Close

End Sub

You'll notice the call to itself about half-way down if the field "is complex", meaning it holds not a value but yet a recordset.

Note too, that the variable Result is handled By Reference to simplify the code.


Having this ready, the DJoin function takes care of the handling of the arguments, the caching, and returning the result. Please study the in-line comments which explain the details of the code:


' Returns the joined (concatenated) values from a field of records having the same key.
' The joined values are stored in a collection which speeds up browsing a query or form
' as all joined values will be retrieved once only from the table or query.
' Null values and zero-length strings are ignored.
'
' If no values are found, Null is returned.
'
' The default separator of the joined values is a space.
' Optionally, any other separator can be specified.
'
' Syntax is held close to that of the native domain functions, DLookup, DCount, etc.
'
' Typical usage in a select query using a table (or query) as source:
'
'   Select
'       KeyField,
'       DJoin("[ValueField]", "[Table]", "[KeyField] = " & [KeyField] & "") As Values
'   From
'       Table
'   Group By
'       KeyField
'
' The source can also be an SQL Select string:
'
'   Select
'       KeyField,
'       DJoin("[ValueField]", "Select ValueField From SomeTable Order By SomeField", "[KeyField] = " & [KeyField] & "") As Values
'   From
'       Table
'   Group By
'       KeyField
'
' To clear the collection (cache), call DJoin with no arguments:
'
'   DJoin
'
' Requires:
'   CollectValues
'
' 2019-06-24, Cactus Data ApS, Gustav Brock
'
Public Function DJoin( _
    Optional ByVal Expression As String, _
    Optional ByVal Domain As String, _
    Optional ByVal Criteria As String, _
    Optional ByVal Delimiter As String = " ") _
    As Variant
    
    ' Expected error codes to accept.
    Const CannotAddKey      As Long = 457
    Const CannotReadKey     As Long = 5
    ' SQL.
    Const SqlMask           As String = "Select {0} From {1} {2}"
    Const SqlLead           As String = "Select "
    Const SubMask           As String = "({0}) As T"
    Const FilterMask        As String = "Where {0}"
    
    Static Values   As New Collection
    
    Dim Records     As DAO.Recordset
    Dim Sql         As String
    Dim SqlSub      As String
    Dim Filter      As String
    Dim Result      As Variant
    
    On Error GoTo Err_DJoin
    
    If Expression = "" Then
        ' Erase the collection of keys.
        Set Values = Nothing
        Result = Null
    Else
        ' Get the values.
        ' This will fail if the current criteria hasn't been added
        ' leaving Result empty.
        Result = Values.Item(Criteria)
        '
        If IsEmpty(Result) Then
            ' The current criteria hasn't been added to the collection.
            ' Build SQL to lookup values.
            If InStr(1, LTrim(Domain), SqlLead, vbTextCompare) = 1 Then
                ' Domain is an SQL expression.
                SqlSub = Replace(SubMask, "{0}", Domain)
            Else
                ' Domain is a table or query name.
                SqlSub = Domain
            End If
            If Trim(Criteria) <> "" Then
                ' Build Where clause.
                Filter = Replace(FilterMask, "{0}", Criteria)
            End If
            ' Build final SQL.
            Sql = Replace(Replace(Replace(SqlMask, "{0}", Expression), "{1}", SqlSub), "{2}", Filter)
            
            ' Look up the values to join.
            Set Records = CurrentDb.OpenRecordset(Sql, dbOpenSnapshot)
            CollectValues Records, Delimiter, Result
            ' Add the key and its joined values to the collection.
            Values.Add Result, Criteria
        End If
    End If
    
    ' Return the joined values (or Null if none was found).
    DJoin = Result
    
Exit_DJoin:
    Exit Function
    
Err_DJoin:
    Select Case Err
        Case CannotAddKey
            ' Key is present, thus cannot be added again.
            Resume Next
        Case CannotReadKey
            ' Key is not present, thus cannot be read.
            Resume Next
        Case Else
            ' Some other error. Ignore.
            Resume Exit_DJoin
    End Select
    
End Function

You'll notice the error handling, which may seem excessive for such a tiny function. The reason is that, with a Collection, you can't check if a key is present or not, only try to look it up and fail if not found, or try to add it and fail if already present, thus proper error handling is mandatory.


For the typical usage, see the examples in the in-line comments at the top of the code block.


Examples


Several queries are included in the attached demo application. One, JobTitleEmailAddressesFull, demonstrates how the first argument can by an expression - which can be much more than just a field name. Here it is used to assemble extended e-mail addresses from each employee's first name, last name, and e-mail address including the mandatory brackets around the e-mail address:


DJoin("[First Name] & ' ' & [Last Name] & ' <' & [E-mail Address] & '>'","[Employees]","[Job Title]='" & [Job Title] & "'",";") AS [E-mail Addresses]

The output will be similar to this:



Another example for a typical usage is the simple transformation of one column of data to one row of data. 

This way it is very easy to list, say, the colours of the T-shirts. To do so, type these commands into the Immediate window and obtain the results shown.

The first is using the default delimiter (a space), the next a star, and the last a tab as delimiter:


? DJoin("[Colour]", "[TShirt]")
Green Red Pink Black Blue

? DJoin("[Colour]", "[TShirt]", , " * ")
Green * Red * Pink * Black * Blue

? DJoin("[Colour]", "[TShirt]", , vbTab)
Green   Red Pink    Black   Blue


The special case: Multi-Value fields


Most developers avoid these if at all possible, for example Colin Riddington (from Mendip Data Systems): Multivalued Fields . . . and why you really shouldn't use them! 


However, they are here, and DJoin can be useful when displaying the values.

First, let's take an example where DJoin cannot add much - using the Products and Suppliers tables of the Northwind sample database from which we can create a simple query:


SELECT 
    Products.[Product Code], 
    Products.[Supplier IDs], 
    DJoin("[Supplier IDs]","[Products]","[ID] = " & [ID] & ""," & ") AS Suppliers
FROM 
    Products;

It will display like this:


The problem is, that the bound value of the Multi-Value field Supplier IDs is the Supplier ID itself, and you cannot retrieve the supplier name without creating a query, and - using DJoin - the output would be very similar to what is displayed natively using the local delimiter (or list separator) which here is a semicolon.


The important detail is, however, that the entries in a Multi-Value field always will be ordered alphabetically. That may fine here and in general when names or similar are joined, but not always. 

For such cases, custom sorting is necessary, and DJoin can help to achieve this.


A custom sorted Multi-Value field

Let's say you have this table with a list of increasing sizes:



and a table of T-shirts available in different sizes obtained by having a RowSource for the Multi-Value field Sizes in the table:


SELECT [Size].[Code] FROM [Size] ORDER BY [Id];


But, as you can see, this only sorts the sizes in the drop-down list, not in the joined list of sizes displayed in the field.


A method to obtain a correctly sorted list to be displayed is to create a query that sorts the sizes by the size, not by the code:



Adjust and tune the query and, when ready, pull the SQL from this:


SELECT TShirt.Id, Size.Code
FROM TShirt
LEFT JOIN [Size] ON TShirt.Sizes.Value = Size.Code
ORDER BY TShirt.Id, Size.Id;

and use it as source in DJoin in a query to display the sizes sorted correctly:



Run this, and - violĂ  -  the T-shirt varieties will display neatly joined like this with the default delimiter, a space:



All the tables and queries are included in the attached demo for you to play with.


Conclusion


It has been demonstrated, that sometimes ancient code can be optimised and improved both regarding speed and versatility. 

Also, even seldom used by developers, it has been shown, that Multi-Value fields can have improved display options added using DJoin.


Code modules and demo application

 

The code has been tested with both 32-bit and 64-bit Microsoft Access 2019 and 365

 

A demo in Microsoft Access 2019 is attached: DJoinDemo 1.0.2.zip

 

The current code can at any time be obtained from GitHub: VBA.DJoin


I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.


Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.


Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.

2
5,583 Views
Gustav BrockMVP
CERTIFIED EXPERT

Comments (3)

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
Excellent! Concatenating values from multiple records to one string is, indeed, a common task that many struggle with. This is a good example of using a collection, and nice that it tests for MV fields (even though people shouldn't use them).  I look forward to studying your caching technique.

Thank you, Gustav
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2023

Author

Commented:
crystal, if you are interested in other examples using a collection as cache, you can study my RowNumber function here:

Sequential Rows in Microsoft Access
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
thank you, Gustav, I will ... saved the link to look at later ~

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.