我在使用MS Access时遇到以下问题：
我有很多表，它们全部只有2列（其中一个列用作PK来标识行-在前3个示例中，它将是“名称”列），我需要将每个列中的每个值连接起来列放入另一张表的一个字段中，例如：
表格1：
Name | Number
--------------------------
Charlie | 1
Charlie | 2
James | 3
James | 4
Michelle | 5
Michelle | 6
表2：
Name | Country
------------------------------
Charlie | Brazil
Charlie | France
James | Japan
Michelle | USA
表3-汇总连接的表：
Name | Number | Country
----------------------------------------------
Charlie | 1,2 | Brazil,France
James | 3,4 | Japan
Michelle | 5,6 | USA
到现在为止，当我在列中只有很少的值时，我能够创建一个使用VBA中的函数将这些值连接到表3中的字段的追加，而不会出现问题。
该函数在以下位置可用：http://allenbrowne.com/func-concat.html和以下内容：
Option Explicit
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
问题是：即使使用表3中的“备注”字段（表1和2现在都使用“文本”，但是我用备注进行了测试，但它们也不起作用），但是我的串联并不能让我串联太多的值，而且我没有得到当我的原始列太大时，表3中需要的每个值。
我的查询：
INSERT INTO
[0A - Totalizador] ( Operadora, NDC_MSISDN )
SELECT DISTINCT
[1A - Paises].Operadora,
ConcatRelated("[NDC]","[1A - NDC_MSISDN]","[Operadora] =""" & [Operadora] & """","[NDC]",", ") AS Expr1,
FROM
[1A - Paises];
错误输出示例：
来源表：
Operadora | NDC
--------------------------
NAME 70100
NAME 70101
NAME 70102
NAME 70103
NAME 801
NAME 802
NAME 80769
NAME 8077
NAME 8078
NAME 80790
NAME 80791
NAME 80792
NAME 808
NAME 8092
NAME 8095
NAME 8099
NAME 9010
NAME 90111
NAME 90112
NAME 9014
NAME 9015
NAME 9016
NAME 90187
NAME 90188
NAME 90189
NAME 90198
NAME 90199
NAME 9021
NAME 9022
NAME 9023
NAME 9024
NAME 9025
NAME 9026
NAME 9027
NAME 9030
NAME 9031
NAME 9032
NAME 9033
NAME 90340
NAME 90346
NAME 90888
NAME 1000
NAME 2000
NAME 3000
NAME 4000
输出表：（已被退回）
Operadora | NDC
------------------------------------------------------
| 70100, 70101, 70102, 70103, 801, 802,
| 80769, 8077, 8078, 80790, 80791, 80792,
| 808, 8092, 8095, 8099, 9010, 90111, 90112,
NAME | 9014, 9015, 9016, 90187, 90188, 90189,
| 90198, 90199, 9021, 9022, 9023, 9024, 9025,
| 9026, 9027, 9030, 9031, 9032, 9033, 90340,
| 90346, 9
输出表（应该返回）：
Operadora | NDC
------------------------------------------------------
| 70100, 70101, 70102, 70103, 801, 802,
| 80769, 8077, 8078, 80790, 80791, 80792,
| 808, 8092, 8095, 8099, 9010, 90111, 90112,
NAME | 9014, 9015, 9016, 90187, 90188, 90189,
| 90198, 90199, 9021, 9022, 9023, 9024, 9025,
| 9026, 9027, 9030, 9031, 9032, 9033, 90340,
| 90346, 90888, 1000, 2000, 3000, 4000
我想不出解决办法...
有人可以帮帮我吗？
最佳答案
我发现可以通过将DISTINCT关键字移到FROM子句中的子查询中来解决此问题：
INSERT INTO
[0A - Totalizador] ( Operadora, NDC_MSISDN )
SELECT
Operadora,
ConcatRelated("[NDC]","[1A - NDC_MSISDN]","[Operadora] =""" & [Operadora] & """","[NDC]",", ") AS Expr1
FROM
(
SELECT DISTINCT Operadora
FROM [1A - Paises]
);
关于sql - INSERT INTO在Access中将串联的表值截断为255个字符，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21051588/