MS Access Tips/Sample and VBA and Blog customize etc...

フォーム/レポートで重複を排除してカウントする関数

Access Club 超初心者 FORUM の下記のスレッド用に作成したサンプルです。
特定のフィールドを基準に重複を省いて、フォームに表示中のレコード件数を..

例えば、下記のようなテーブルあるとして、

月会費
会員ID月号月会費受け取り日付
12011年5月号2011/05/02
12011年6月号2011/06/03
12011年7月号2011/07/02
22011年5月号2011/05/01
22011年6月号
22011年7月号
32011年5月号2011/05/02
32011年6月号2011/06/02
32011年7月号

これをレコードソースとするフォームやレポートで、会員は何名いるか、あるいは、会費の未収(月会費受け取り日付 がNull)がある会員は何名いるか、を表示したい。フォームやレポートにはフィルタが掛かっている場合もあり、その場合は、表示されているデータを対象に集計したい。

というような要件です。上記のデータの場合、希望する結果としては、ユニーク会員数 3名、会費未収会員数 2名 となります。

コントロールソースにDCount関数を利用した式を設定して表示する方法と、ユーザー定義関数を作成してそれを利用する方法を紹介します。

難易度:

DCount関数を利用する方法

下記のような「会員マスタ」テーブルが存在するのが前提です。(正規化された設計をしていれば存在すると思います。)

会員マスタ
会員ID氏名
1佐藤
2鈴木
3高橋

月会費をレコードソースとする帳票フォームを作成。そのフォームフッターに下記のテキストボックスを配置。

会員IDを基準に重複を排除した件数(ユニーク会員数)

プロパティ設定値
名前会員数
コントロールソース=DCount("会員ID","会員マスタ","会員ID In (SELECT 会員ID FROM 月会費 WHERE " & Format(IIf([FilterOn],[Filter],""),"&;""True""") & ")")

月会費受け取り日付がNullのデータのある会員数(未収ユニーク会員数)

プロパティ設定値
名前未収会員数
コントロールソース=DCount("会員ID","会員マスタ","会員ID In (SELECT 会員ID FROM 月会費 WHERE 月会費受け取り日付 Is Null AND " & Format(IIf([FilterOn],[Filter],""),"&;""True""") & ")")

月会費受け取り日付にNullのデータがない会員数(領収ユニーク会員数)

プロパティ設定値
名前領収会員数
コントロールソース=[会員数]-[未収会員数

DCount関数の第3引数の抽出条件に、サブクエリを含めるのがポイントですね。フィルタの抽出条件は、Filterプロパティを参照して取得できます。

これで、ユニーク会員数か表示できます。フィルタを掛けたり変更したりすると自動で再計算されます。また、データを更新または追加した場合も自動で再計算されます。

ユーザー定義関数 UniqueCount

上記のDCountを利用する方法は、かなり複雑な式になりますので、メンテナンスが面倒そうです。もう少しシンプルな式で記述したいですね。

そこで、汎用的に使えるユーザー定義関数を作成してみました。

標準モジュールに下記の関数を作成します(ダブルクリックで選択して、コピーして貼り付けてください。)。

2011/07/26 修正: レコードソースにSQLを設定していて、最後が";"の時、エラーになるバグを修正

使用例

フォームのフォームヘッダーかフォームフッターのテキストボックスのコントロールソースに下記のように設定します。

会員IDを基準に重複を排除した件数(ユニーク会員数)

月会費受け取り日付がNullのデータのある会員数(未収ユニーク会員数)

フィルタを掛けたり変更したりすると自動で再計算されますが、データを更新・追加・削除した場合は再計算されませんので、フォームの更新後処理で強制的に再計算させてください。

レポートヘッダー、レポートフッターに配置する時も上記と同じ式で使えます。

グループヘッダー/フッターの場合は、下記のようにグループのキーフィールドで抽出条件を設定すれば、グループ内での集計になります。

サンプルファイルが下記からダウンロードできます。
VBAUniqueCount_07.zip (Access 2007 形式 - 38kb)
VBAUniqueCount.zip (Access 2002-2003 形式 - 33kb)
VBAUniqueCount_2k.zip (Access 2000 形式 - 31kb)

拍手する

1 Comments

bazzini says...""

お世話になります。
冒頭で上げられているAccessフォーラムの質問者です。
今さらながら当該スレッドに追記があったことを知りましてお礼を申し上げに来ました。(向こうはもう過去ログになってしまっているため)

自分は「もうVBAで書こう・・」的な事を言いましたが、結局DCountで弄ってたら特に問題ない動作してたのでそちらで実装することにしました。
正直このやり方は自分では絶対に出てこなかったっと思うので非常に助かりました。
ありがとうございました。

ついでに追伸
冒頭のフォーラムURLはもう繋がらない?ようです。
今はこちら?過去ログになるとURLが変わるのかな?
http://www.accessclub.jp/bbs3/0588/superbeg169877.html

後、
・会員IDを基準に重複を排除した件数(ユニーク会員数)
・月会費受け取り日付がNullのデータのある会員数(未収ユニーク会員数)
の中身が同じになってしまっています。
(サンプルの中の記述はもちろん問題なし)

2011.08.13 10:41 | URL | #- [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/314-b008182d
該当の記事は見つかりませんでした。