我有一张桌子,叫做TBL.它有两列,称为A和B.现在在查询中,我需要一列作为A,其他列应该是TBL中与A对应的所有B的逗号分隔列表.
例如TBL就是这样
例如TBL就是这样
1阿尔法
2 Beta
1伽玛
1达美
查询结果应该是
1 Alpha,Gamma,Delta
2 Beta
这种类型的事情在存储过程中与光标非常容易.但是我无法通过MS Access进行操作,因为显然它不支持存储过程.
有没有办法在MS访问中运行存储过程？还是有办法通过sql来运行这种类型的查询
解决方法
您可以将记录与用户定义函数(UDF)连接起来.
以下代码可以“按原样”粘贴到标准模块中.您的示例的sql将是：
SELECT tbl.A,Concatenate("SELECT B FROM tbl
WHERE A = " & [A]) AS ConcA
FROM tbl
GROUP BY tbl.A
这个代码是由DHookom,Access MVP,从http://www.tek-tips.com/faqs.cfm?fid=4233开始
Function Concatenate(pstrsql As String,_ Optional pstrDelim As String = ",") _ As String 'example 'tblFamily with FamID as numeric primary key 'tblFamMem with FamID,FirstName,DOB,... 'return a comma separated list of FirstNames 'for a FamID ' John,Mary,Susan 'in a Query '(This sql statement assumes FamID is numeric) '=================================== 'SELECT FamID,'Concatenate("SELECT FirstName FROM tblFamMem ' WHERE FamID =" & [FamID]) as FirstNames 'FROM tblFamily '=================================== ' 'If the FamID is a string then the sql would be '=================================== 'SELECT FamID,'Concatenate("SELECT FirstName FROM tblFamMem ' WHERE FamID =""" & [FamID] & """") as FirstNames 'FROM tblFamily '=================================== '======For DAO uncomment next 4 lines======= '====== comment out ADO below ======= 'Dim db As DAO.Database 'Dim rs As DAO.Recordset 'Set db = CurrentDb 'Set rs = db.OpenRecordset(pstrsql) '======For ADO uncomment next two lines===== '====== comment out DAO above ====== Dim rs As New ADODB.Recordset rs.Open pstrsql,CurrentProject.Connection,_ adOpenKeyset,adLockOptimistic Dim strConcat As String 'build return string With rs If Not .EOF Then .MoveFirst Do While Not .EOF strConcat = strConcat & _ .Fields(0) & pstrDelim .MoveNext Loop End If .Close End With Set rs = Nothing '====== uncomment next line for DAO ======== 'Set db = Nothing If Len(strConcat) > 0 Then strConcat = Left(strConcat,_ Len(strConcat) - Len(pstrDelim)) End If Concatenate = strConcat End Function
