access合拢多行列数据到一行，类似于mysql的group_concact()函数

?

?

Concatenate fields in same table

(Q)??? I need to concatenate a field in the format "Value1; Value2;Value3" etc. for each unique value of another field in the same table.? How canI do this?

(A)??? Using the fConcatFld function,? in the Northwind database,the following query should return a concatenated list of all CustomerIDs if you group byContactTitle.

使用方式：
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 RecordsetDim lovConcat As Variant, loCriteria As StringDim loSQL As StringConst 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 FunctionErr_fConcatFld:    MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description    Resume Exit_fConcatFldEnd Function'************ Code End **********