正規化されたテーブルを使えば、Accessの標準関数が使えます。
こんなユーザー関数は全く必要ありません。
2次元方向にも計算します。 つまり、面で計算もします。
定義域集計関数に対し、正規化されていないテーブルの複数フィールドを集計する関数です。
D(Domain)系関数に対し、H(horizontal)系関数ということで(^^;
引数
各関数共通 名前| 説明 --------------------------------------------------------------------------------------------- TBL|対象テーブル/クエリ名 Wherecondition|集計対象の抽出条件。Where句からWhereを除いた文字列。 | Nullを設定するとテーブル全体が対象。省略は不可。 ParamArray FLD()|集計するフィールドを左から数えた順番に ,(カンマ)区切りで指定。個数は任意の数。
フィールドの合計
HSum関数Function HSum(TBL As String, Wherecondition As Variant, ParamArray Fld()) As Variant Dim strSQL As String Dim Var As Variant strSQL = "SELECT * FROM " & TBL & " WHERE " & Nz(Wherecondition, "True") With CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly) Do Until .EOF For Each Var In Fld HSum = HSum + Nz(.Fields(Var - 1), 0) Next Var .MoveNext Loop End With End Functionフィールドの最大
HMax関数Function HMax(TBL As String, Wherecondition As Variant, ParamArray Fld()) As Variant Dim strSQL As String Dim Var As Variant strSQL = "SELECT * FROM " & TBL & " WHERE " & Nz(Wherecondition, "True") With CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly) HMax = Nz(.Fields(Fld(0) - 1), 0) Do Until .EOF For Each Var In Fld If HMax < Nz(.Fields(Var - 1), HMax) Then HMax = Nz(.Fields(Var - 1), HMax) End If Next Var .MoveNext Loop End With End Functionフィールドの最小
HMin関数Function HMin(TBL As String, Wherecondition As Variant, ParamArray Fld()) As Variant Dim strSQL As String Dim Var As Variant strSQL = "SELECT * FROM " & TBL & " WHERE " & Nz(Wherecondition, "True") With CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly) HMin = Nz(.Fields(Fld(0) - 1), 0) Do Until .EOF For Each Var In Fld If HMin > Nz(.Fields(Var - 1), HMin) Then HMin = Nz(.Fields(Var - 1), HMin) End If Next Var .MoveNext Loop End With End Functionフィールドの平均
HAvg関数Function HAvg(TBL As String, Wherecondition As Variant, ParamArray Fld() As Variant) As Variant On Error Goto Err_Hand Dim strSQL As String Dim Var As Variant Dim lngC As Long strSQL = "SELECT * FROM " & TBL & " WHERE " & Nz(Wherecondition, "True") With CurrentDb.OpenRecordset(strSQL,, dbReadOnly) Do Until .EOF For Each Var In Fld HAvg = HAvg + Nz(.Fields(Var - 1), 0) Next Var .MoveNext Loop lngC = .RecordCount * (UBound(Fld) + 1) End With HAvg = HAvg / lngC Exit Function Err_Hand: If lngC = 0 Then MsgBox "条件( " & Wherecondition & " )に合致するレコードが有りませんでした。" Else MsgBox Err.Number & " : " & Err.Description End If End Function使用方法
ID F1 F2 F3 F4 ----------------------------------- 1 10 20 30 40 2 20 30 40 50 3 30 40 50 60 というテーブル T1 に対し Print HSum("T1", Null, 3, 4, 5) 'テーブル全体のF2, F3, F4 の合計 360 Print HSum("T1", "ID = 2", 2, 4, 5) 'ID = 2 のレコードのF1, F3, F4 の合計 110 Print HAvg("T1", "ID 2", 3, 4, 5) 'ID = 2 以外のF2, F3, F4の平均 40 ---------------------------------------------------------------------------------- クエリデザイン フィールド:|| ID || 最小:HMin("T1","ID=" & [ID],2,3,4,5) || 最大:HMax("T1","ID=" & [ID],2,3,4,5) の実行結果 ID 最小 最大 1 10 40 2 20 50 3 30 60 ※ レコードごとのF1, F2, F3, F4の中の最大と最小
#1 投稿者: emipan (2011/08/25 - 5:20 午後)
このページに記載の通りにやると、うまくいくのですが、
業務用のテーブルを使うと「実行時エラー’3061’パラメータが少なすぎます。1を指定してください。」エラーが出ます。
With CurrentDb.OpenRecordset(strSQL, dbOpenForwardOnly)
でエラーになって、
strSQLにはKEYとなるデータが入っているようですが、
dbOpenForwardOnlyには、=8というデータが入っています。
このページに記載のテーブルを参考に、
日付の比較もうまくいったし、
空欄があってもうまくいったし、
何が問題なのかわかりません。
何が考えられるでしょうか。
#2 投稿者: accesskid (2011/08/29 - 2:21 午後)
ここの関数のうちの、どの関数をどんな風に使いましたか?
集計の対象はクエリですか?
クエリだとしたら、パラメータクエリではないですか?
実際に使われる式を、差支えない形でここに貼ることは出来ますか?