MS access(2003)是否具有与存储过程相当的功能.我想在MS Acceess中运行复杂的查询 [英] Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess

问题描述

我有一张桌子，叫做TBL.它有两列，分别称为A和B.现在在查询中，我需要一列作为A，而另一列应该是用逗号分隔的所有B列表，其中所有B与TBL中的A相对. 例如TBL就是这样

I have a table, call it TBL. It has two columns,call them A and B. Now in the query I require one column as A and other column should be a comma seprated list of all B's which are against A in TBL. e.g. TBL is like this

1个Alpha

2 Beta

1个伽玛

1个三角洲

查询结果应为

1个Alpha，Gamma，Delta

1 Alpha,Gamma,Delta

2 Beta

使用存储过程中的游标很容易做到这一点.但是我无法通过MS Access进行此操作，因为它显然不支持存储过程. 有没有一种方法可以在MS Access中运行存储过程?还是有一种方法可以通过SQL运行这种类型的查询

This type of thing is very easy to do with cursors in stored procedure. But I am not able to do it through MS Access, because apparently it does not support stored procedures. Is there a way to run stored procedure in MS access? or is there a way through SQL to run this type of query

推荐答案

您可以将记录与用户定义函数(UDF)串联起来.

You can concatenate the records with a User Defined Function (UDF).

下面的代码可以按原样"粘贴到标准模块中.您的SQL示例为:

The code below can be pasted 'as is' into a standard module. The SQL for you example would be:

SELECT tbl.A, Concatenate("SELECT B  FROM tbl
        WHERE A = " & [A]) AS ConcA
FROM tbl
GROUP BY tbl.A

此代码由Access MVP DHookom提供，摘自 http://www.tek-tips.com/faqs.cfm?fid=4233

This code is by DHookom, Access MVP, and is taken from 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 

这篇关于MS access(2003)是否具有与存储过程相当的功能.我想在MS Acceess中运行复杂的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！