从表或查询的一个字段中联接（合并）值

历史 (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.

在VBA中，这可以循环执行，在此循环中，您将逐步浏览有序的记录集并构建联接的值，但是，出于大多数实际目的，您将首选查询，该查询会将结果作为记录集返回。 这样的查询可以用作表单或报表的源。

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
  多种源类型选择-如纯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:

但是首先，要讨论的函数DJoin的命名表示对本机域聚合函数的熟悉-DLookup ，DCount等-因为它将来自多个记录的一个字段的值聚合到一个字符串中-与Join类似对数组执行操作，例如：

' 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:

若要从表中创建类似的输出，请从Northwind示例数据库中获取“ 雇员”表，并使用以下查询提取电子邮件地址：

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

and save it as JobTitleEmailAddress. It will output:

并将其另存为JobTitleEmailAddress 。 它将输出：

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:

并将其另存为JobTitleEmailAddresses 。 它将输出：

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.

为了加快重复呼叫的速度，可以使用缓存。 您可能会问为什么会发生重复调用，而对于一个以小表为源的单个请求，可能不会发生重复调用。 但是对于大型表，由于查询优化器和Access读取记录的顺序而使我们无法控制，因此每个键最多可以调用该函数5次。

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

为了避免重复通话的额外费用（时间），每个键的结果都可以存储在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:

SpeedTest函数（来自下面的附加演示）揭示-对于约170,000条具有424个键的记录的大表-首次运行时速度提高了约25％ 。 重复通话的速度提高了大约16倍-速度显着提高：

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

方法	第一次电话	后续通话
Concat相关	2.15秒	2.14秒
DJoin	1.59秒	0.09秒

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

艾伦·布朗 （ Allan Browne）的示例函数ConcatRelated可以在这里找到。 它也包含在演示中（请参阅下文）。

以SQL为来源 (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.

通常，您将没有确切的表或查询来传递要加入的数据。 如果是这样，则仅出于此目的必须创建并保存一个新查询。 使用DJoin，您可以将普通的Select查询作为源传递，该查询允许进行各种排序，过滤和分组。

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.

如上所述，DJoin缓存所有结果以减少重复调用所花费的时间。 这样的效果是，如果将查询用作表单的源，则浏览的速度几乎与浏览不带DJoin表达式的干净表或查询的速度相同。

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.

演示中包含两个查询，即Concat和Join ，您可以打开和浏览它们。

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

要清除缓存，只需调用不带参数的 DJoin：

DJoin 

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

这可以在表单或报表的OnClose事件中完成。

DJoin-功能 (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:

为了高效处理多值字段并避免重复代码，DJoin使用了一个辅助函数，当要连接的字段是多值字段时，该函数将通过递归调用自身：

' 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.

还要注意，变量Result 通过引用进行处理以简化代码。

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:

准备就绪后，DJoin函数会处理参数的处理，缓存以及返回结果。 请研究解释代码细节的在线注释：

' 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.

您会注意到错误处理，对于这么小的函数来说，这似乎过分了。 原因是，对于Collection，您无法检查密钥是否存在，只能尝试查找它，如果找不到则失败，或者尝试添加它，如果已经存在则失败，因此可以进行正确的错误处理强制性的。

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:

随附的演示应用程序中包含几个查询。 其中一个名为JobTitleEmailAddressesFull ，它演示了表达式可以如何使用第一个参数-它可以不仅仅是字段名。 在这里，它用于根据每个员工的名字，姓氏和电子邮件地址（包括电子邮件地址周围的强制括号）来组合扩展的电子邮件地址：

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.

这样，很容易列出T恤的颜色。 这样做，请在立即窗口中键入这些命令并获得显示的结果。

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. However, they are here, and DJoin can be useful when displaying the values.

大多数开发人员都尽可能避免使用这些方法。 但是，它们在这里，并且DJoin在显示值时很有用。

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:

首先，让我们举一个DJoin不能增加太多的示例-使用Northwind示例数据库的Products和Suppliers表，从中可以创建一个简单的查询：

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.

问题在于，“多值”字段“供应商ID”的界限值是“ 供应商ID”本身，并且您不能在不创建查询的情况下检索供应商名称 ，并且-使用DJoin-输出将与本机显示的内容非常相似使用本地定界符（或列表分隔符），这里是分号。

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.

在这种情况下，必须进行自定义排序 ，而DJoin可以帮助实现这一点。

自定义排序的多值字段 (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:

并通过在表中的多值字段大小的行来源获得不同尺寸的T恤表：

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:

调整和调整查询，并准备好从中提取SQL：

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:

并在查询中将其用作DJoin中的源以显示正确排序的大小：

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

运行此命令，然后-violà-这样的T恤品种将使用默认的分隔符（空格）像这样整齐地显示：

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.

而且，即使开发人员很少使用，也已经显示出，多值字段可以使用DJoin添加改进的显示选项。

代码模块和演示应用程序 (Code modules and demo application)

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

该代码已经过32位和64位Microsoft Access 2019和365测试 。

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

随附Microsoft Access 2019中的演示： DJoinDemo 1.0.2.zip

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

当前代码可随时从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.

注意 ：如果您需要有关此主题的更多“支持”，请考虑使用Experts Exchange 的“提问”功能。 我会监督提出的问题，并很高兴与其他电子工程师一起为以这种方式提出的问题提供所需的任何其他支持。

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

如果您认为本文对EE成员有用且有价值，请不要忘记按下“竖起大拇指”按钮。

翻译自: https://www.experts-exchange.com/articles/33612/Join-concat-values-from-one-field-from-a-table-or-query.html