sql - INSERT INTO 在 Access 中将连接的表值截断为 255 个字符

Question

我在使用 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 中的 Memo 字段(表 1 和 2 现在使用 Text，但我用 memo 进行了测试，它们也不起作用)，我的连接不允许我连接太多的值，并且当我的原始列太大时，我没有得到表 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

我想不出解决这个问题的方法...有人可以帮帮我吗？

Answer 1

我发现我可以通过将 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]
    );