access表如何在查询中按某字段归类后将另一个字段的各条记录的值显示在计算​字段中

Question

在查询中，按某字段归类后将另1字段的各条记录的值显示在一个计算字段中
假设3个字段名为ID/C/T
各条记录为
1/A/a1
2/B/b1
3/B/b2
4/C/c1
5/C/c2
6/C/c3
通过查询转换为：
A/a1
B/b1,b2
C/c1,c2,c3
我是想把这个查询作为子窗体的数据源，所以只能用选择查询，而不能用动作查询

Answer 1

模块中定义函数：
Public Function GroupConcat(sColumn As String, sTable As String, Optional sCriteria As String, Optional sDelimiter As String = ",")
On Error GoTo ErrHandler
Dim rs As New ADODB.Recordset
Dim sSQL As String
Dim sResult As String
sResult = ""
sSQL = "select " & sColumn & " from " & sTable
If sCriteria <> "" Then
sSQL = sSQL & " where " & sCriteria
End If
rs.Open sSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do While Not rs.EOF
If sResult <> "" Then
sResult = sResult & sDelimiter
End If
sResult = sResult & rs.Fields(0).Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
GroupConcat = sResult
Exit Function
ErrHandler:
If rs.State <> adStateClosed Then
rs.Close
End If
Set rs = Nothing
GroupConcat = Err.Number & " : " & Err.Description

End Function
---------------------------------------------------------------------------------------
select C, GroupConcat('T','表名','C=' & "'" & C & "'") as T
from 表名
group by C;
---------------------------------------------------------------------------------------------
http://zhidao.baidu.com/link?url=2VYCdb4cEvVbktcNr0ZaSmxbmFCots-n7wn0eru2hDYp3hO4gXdaX5t9ZA3PPmn-iHfJuoZ4Ye3RJC80USuwNK本回答被提问者采纳