access合并多行列数据为一行

```
使用方式：
SELECT ContactTitle, fConcatFld("Customers","ContactTitle","CustomerID","string",[ContactTitle]) AS Customers FROM Customers GROUP BY ContactTitle;

参数说明：

fConcatFld参数说明

stTable As String 表名称
 _stForFld As String,  查询的条件字段名称
_stFldToConcat As String,  合并的字段名称
_ stForFldType As String, 合并字段的类型
_vForFldVal As Variant 合并字段的查询条件

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'

Function fConcatFld(stTable As String, _
                    stForFld As String, _
                    stFldToConcat As String, _
                    stForFldType As String, _
                    vForFldVal As Variant) _
                    As String
'Returns mutiple field values for each unique value
'of another field in a single table
'in a semi-colon separated format.
'
'Usage Examples:
'   ?fConcatFld(("Customers","ContactTitle","CustomerID", _
'                "string","Owner")
'Where  Customers     = The parent Table
'       ContactTitle  = The field whose values to use for lookups
'       CustomerID    = Field name to concatenate
'       string        = DataType of ContactTitle field
'       Owner         = Value on which to return concatenated CustomerID
'

Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

On Error GoTo Err_fConcatFld
    
    lovConcat = Null
    Set lodb = CurrentDb
    
    loSQL = "SELECT [" & stFldToConcat & "] FROM ["
    loSQL = loSQL & stTable & "] WHERE "
    
    Select Case stForFldType
        Case "String":
            loSQL = loSQL & "[" & stForFld & "] =" & cQ & vForFldVal & cQ
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long

loSQL = loSQL & "[" & stForFld & "] = " & vForFldVal
        Case Else
            GoTo Err_fConcatFld
    End Select
    
    Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
    
    'Are we sure that duplicates exist in stFldToConcat

With lors
        If .RecordCount <> 0 Then
            'start concatenating records

Do While Not .EOF
                lovConcat = lovConcat & lors(stFldToConcat) & "; "
                .MoveNext
            Loop
        Else
            GoTo Exit_fConcatFld
        End If
    End With
        
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;

fConcatFld = Left(lovConcat, Len(lovConcat) - 2)

Exit_fConcatFld:
    Set lors = Nothing: Set lodb = Nothing
    Exit Function

Err_fConcatFld:
    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
    Resume Exit_fConcatFld
End Function
'************ Code End **********
```

最后更新于 2021-04-10 18:23:14 并被添加「」标签，已有 2540 位童鞋阅读过。

本站使用「署名 4.0 国际」创作共享协议，可自由转载、引用，但需署名作者且注明文章出处

vps 之家

access合并多行列数据为一行

此处评论已关闭