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.
So, we have some areas for improvement and added flexibility:
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.
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).
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
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.
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.
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
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.
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.
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.
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.
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.
Comments (3)
Commented:
Thank you, Gustav
Author
Commented:Sequential Rows in Microsoft Access
Commented: