ACCESS中的参数查询与自定义函数的实现

 

1.       带参数的查询

有一个数据表，表名为myTEST，表结构及内容如下：

需要创建一个查询实现功能：指定月的数据统计。因为要用于GROUP BY ，所以无法在创建查询后使用条件来完成指定月的数据统计，例如：

创建查询：myView  查询的功能：显示指定月每天每个项目的完成数量情况（这里还需要用于交叉表），需要显示的结果如下：

如果发实现这样的结果，用简单的交叉表和统计功能可以实现，但如果你需要单独列出指定的月份（用Where 或having）则不能实现，这个时候我们可以利用ACCESS数据库中的PARAMETERS 来实现，详细的语句如下：

PARAMETERS pMonth Short;

TRANSFORM Sum(mytest.iNum) AS 总数

SELECT mytest.item AS 项目

FROM mytest

WHERE Month(dDate)=pMonth

GROUP BY mytest.item

PIVOT mytest.dDate;

在这里我们要注意：

l         TRANSFORM 语句，它是用来生成交叉表的，详细说明请参考ACCESS帮助文件

l         PARAMETERS 声明，是用来定义查询中的参数，详细说明请参考ACCESS帮助文件

 

2.       在查询或SQL语句中使用自定义函数功能

在大型的数据库系统中（例如SQLServer），可以使用自定义函数功能来扩展数据处理功能，而在ACCESS数据库中没有存储过程、函数、触发器等功能。如何在SQL语句或定义查询使得到自定义的函数，用于扩展自己的数据库功能，可以使用ACCESS提供的模块功能来实现

首先，我们新建一个模块，取名为modMain（可选意取）。然后打开这个模块，在打开VBA环境下编写相应的代码来完成需要的功能。例如：需要获取某个列的所有值，并生成一个字串：

Option Compare Database

Public Function CombStr(TableName As String, FieldName As String, GroupField As String, GroupValue As String) As String

    Dim ResultStr As String

    Dim rs As Recordset

        Set rs = CurrentDb.OpenRecordset("select " & FieldName & " from " & TableName & " where " & GroupField & "='" & GroupValue & "'")

        If rs.RecordCount > 0 Then

            Do While Not rs.EOF

                If InStr(ResultStr, rs.Fields(0).Value) = 0 Then ResultStr = ResultStr & "," & rs.Fields(0).Value

                rs.MoveNext

            Loop

        End If

        If ResultStr <> "" Then ResultStr = Mid(ResultStr, 2)

        CombStr = ResultStr

End Function

下面我们可以看下实现的效果：

首先创建一数据表，取名person

然后创建一个查询，取名Person_V，内容如下：

SELECT Company, combstr("Person","Name","Company",Company) AS CombName, combstr("Person","sex","Company",Company) AS CombSex

FROM Person GROUP BY Company;

执行查询Person_V，结果如下：