- 定義域集計関数の基本が分かる
- 実践での使い方をイメージできる
Access関数の使い方をシリーズで投稿しています。
前回は日付関数の使い方を解説しました。
第4回目の今回は『定義域集合関数』です。
もくじ
定義域集合関数は、「D」で始まるAccessの関数です。
その中にはDSumやDlookupがありますが、引数の設定方法は共通しているので、コツをつかめば全ての定義域集合関数にそのまま使えます。
本記事では、まずは定義域集合関数の基本から始めて、実例と実務での使いどころについて、できるだけくわしく解説していきます。
実務では、Accessフォームで定義域集合関数を使った事例をあげましたので、きっとイメージしやすいと思います。
Accessデータベースを構築するときに、定義域集合関数を知っておくと便利な場面もありますので、気になる方はこの機会に学習してみましょう。
定義域集合関数の基本
まずはAccessの式ビルダで定義域集合関数の引数を確認してみましょう。
新しいクエリを作り、式ビルダを開きます。
「組み込み関数」→「集合定義域関数」の順にクリックし、表示された関数の中の一つをクリックします。
ここでは一番上のDAvg関数を選択しました。
式の構文が次のように表示されました。
- DAvg(≪Expression≫,≪Domein≫,≪Criteria≫)
引数の設定が難しいように感じてしまいますが、SQLの基本例文と照らし合わせると理解しやすいです。
- 集合定義域関数・・・DAvg("名前",”名簿”,"都道府県='大阪府'")
- SQL文・・・SELECT 名前 FROM 名簿 WHERE 都道府県 = ‘大阪府’;
つまり、引数の順番がSQLと全く同じということです。
ここを意識すると、引数の設定ミスが減るので、ぜひ頭に入れておきましょう。
では、それぞれの引数の設定について解説していきます。
第一引数-Expression
結果を返したいフィールド名を一つだけ指定することができます。
「“名前”」や「’名前’」のように、前後を「”(ダブルクオーテーション)」か「’(シングルクオーテーション)」で必ずはさみましょう。
忘れるとエラーが返されてしまいます。
SQL文でいうところの「SELECT」の直後に相当する部分です。
第二引数-Domein
元データとして使うテーブルを指定します。
SQL文の「FROM」の直後に相当する部分で、例文では「”名簿”」か「’名簿’」とします。
ここではSQL文を指定できません。
場合によっては、専用のクエリを作ってあげる必要があります。
第三引数-Criteria
条件を指定します。
SQL文の「WHERE句」に相当する部分で、例文を使うと次のような書き方をします。
- 大阪府のみに固定したい場合 → ”都道府県=’大阪府’ ”
- 都道府県によって結果を変えたい場合 → “都道府県=’ ” & [都道府県] &” ’ ”
WHERE句はデータ型で指定方法が変わりますが、定義域集合関数も同じです。
条件を固定しない場合は、「&」と「[ ]」を使ってフィールドを指定してあげましょう。
3つの引数を組み合わせると、以下のような式になりました。
- DCount(“名前”,”名簿”,”都道府県=’大阪府’ ”)
- DCount(“名前,”名簿”,”都道府県=’ “ & [都道府県] & “ ’ ”)
引数の順番もSQL文と同じとイメージしておくと、都度調べなくともスムーズに式を作れます。
SQL文の使い方をおさらいしたい方は、こちらの記事もあわせてどうぞ。
≫【SQL学習入門】1回目:Accessで使えるSELECT文(前編)
それでは、実際にAccessデータベースを使った実演に進みましょう。
≫サンプルデータベースはこちら
データベースを開いてみると、4つのテーブル、3つのクエリ、2つのフォームが入っています。
リレーションシップを開いてみると、全てのテーブルがつながっています。
売上テーブルには1000件のレコードを入力しておきました。
これから定義域集合関数を使って、1000件のレコードをいろいろな方法で集計してみます。
初めて触る方は、当記事を参考にして実際に手を動かしてみることをオススメします。
定義域集合関数の実例1-DSum・Dcount
定義域集合関数は全部で12種類ありますが、なかでも「DSum」と「DCount」の2種類が使うことが多くて役に立つ関数です。
これから実際にDSumとDCount関数の使い方について、3つの集計方法に分けて解説していきますね。
集計方法1-シンプルな集計
まず押さえときたいことは、シンプルに合計や個数を集計する方法です。
新しいクエリ「Q01_DSum_1」に２つのフィールドを追加します。
- テーブル「T_担当」の「名前」フィールド
- DSum関数を使った「集計」フィールド
集計フィールドをズームで確認してみると、DSum関数には以下のようにして入力されています。
- 集計:DSum(“金額”,”Q00_売上表示用”,”担当ID=” & [T_担当].[担当ID])
データシートビューを確認すると、担当者ごとの合計金額が計算されているのが確認できました。
DSum関数をDCount関数に置き換えてみましょう。
件数を集計することができました。
集計方法2-累計を計算する
DSumやDCountでは累積の計算ができます。
ただ合計や件数を集計するだけなら、集計クエリでも代用できます。
しかし、Accessで累積を計算できるのはDSumとDCountだけなので、必ず知っておきたいテクニックですね。
新しいクエリ「Q01_DSum_2」に3つのフィールドを追加します。
- クエリ「Q00_売上表示用」の「年月」フィールド
- 合計を集計した「単月」フィールド
- 累積を集計した「累積」フィールド
単月と累積の違いはWhere条件の書き方です。
- 単月:DSum(“金額”,”Q00売上表示用”,”年月=’” & [年月] & “’”)
- 累積:DSum(“金額”,”Q00売上表示用”,”年月<=’” & [年月] & “’”)
累積側には不等号を入れるのが大事なポイントです。
データシートビューに切り替えてみると、累積が計算されているのが分かりました。
クエリのDSum関数をDCount関数に置き換えると、件数を計算できます。
集計方法3-複数の条件を組み合わせる
1つの条件だけでなく、複数の条件を組み合わせたいときもありますよね。
複数条件についても、SQLのWhere句と全く同じ方法で設定することが可能です。
新しいクエリ「Q01_DSum_3」に４つのフィールドを追加してみます。
- クエリ「Q00_売上表示用」の「年月」フィールド
- クエリ「Q00_売上表示用」の「名前」フィールド
- DSumを使った「金額_単月」フィールド
- 同じくDSumを使った「累積」フィールド
「年月」と「名前」の２つを条件にしますので、DSum関数の書き方は下の通りにです。
- 金額_単月:DSum(“金額”,”Q00売上表示用”,”年月=’” & [年月] & “’ AND 名前=’” & [名前] & “’”)
- 累積:DSum(“金額”,”Q00売上表示用”,”年月<=’” & [年月] & “’ AND 名前=’” & [名前] & “’”)
累積については、年月だけに不等号をつけます。
デーシートビューに切り替えると、更に名前別に細分化できることが確認できました。
名前を伊藤さんで絞ってみると、累積で正しく計算されているようです。
定義域集合関数の実例2-DMax
では、次に最大値を計算する「DMax」を使ってみましょう。
新しいクエリ「Q03_DMax_1」に２つのフィールドを追加します。
・テーブル「T_担当」の「名前」フィールド
・DMax関数を使った「最大売上」フィールド
最大売上フィールドの中身をズームで表示してみます。
・最大売上:DMax(“金額計”,”Q00_日別売上”,”名前=” & [名前] & “’”)
データシートビューで確認すると、名前別の最大売上を表示できました。
合計やカウントを集計する時はクエリ「Q00_売上表示用」を使っていましたが、Dmaxの場合はクエリ「Q00_日別売上」を使っています。
「DSub」や「DCount」とは違い、「DMax」は集計をかけずに対象のデータだけを抜き出す関数ですので、使わないフィールドを除外してまとめる必要があります。
定義域集合関数の実例3-DLookup
最後にDLookup関数を使ってみましょう。
クエリ「Q03_DMax_1」にフィールド「検索日付」を追加します。
検索日付フィールドの中身をズームで表示してみます。
・検索日付:Dlookup(“日付”,”Q00_日別売上”,”金額計=” & [最大売上] & “ AND 名前 = “ & [最大売上] & “ AND 名前 = ‘” & [名前] & “’”)
データシートビューで確認すると、最大売上を出した日付を表示できました。
ここまで記事を読んでくださった方は、Dkookup関数はExcelのVlookupとは全く使い方が違うということを理解して頂けましたよね。
Vlookup関数と同じ感覚でDlookup関数を使おうとしても、使いこなせることはできません。
SQL文の感覚をつかんでからDlookup関数に挑戦すれば、スムーズに使えるようになっているはずです。
Accessフォームで定義域集合関数を使う
定義域集合関数には下のようなデメリットがあるので、使う機会が少ないのではないでしょうか？
- 使い方にやや個性(クセ)がある
- 定義域集合関数のほとんどが集計クエリで代用できてしまう
しかし、局所的には定義域集合関数を使った方が便利なこともあります。
- 累積を一発で計算できる
- Accessフォームに計算結果を表示できる
累計については、DSum関数で解説した通りです。
Accessフォームでの関数の使い方を確認するためにも、サンプルデータベースのフォーム「売上一覧M」を開いてみましょう。
フォームの下にあるテキストボックスには、定義域集合関数が使われています。
コンボボックスで名前を選ぶと、テキストボックスのデータが変わります。
フォームをデザインビューに切り替えると、テキストボックスに関数が挿入されているのが分かります。
どんな関数になっているのか、プロパティからズームを開いて確認してみましょう。
IIfを使って、コンボボックスが空のときと、そうでない時には結果が変わるよう、数式を作ってありますので確認しておきましょう。
まとめ
今回は、Accessの「定義域集合関数」について解説しました。
関数の名前自体が難しそうで、とっつきにくい感じがしますが、うまく使いこなせると便利です。
ちょっと頑張ってマスターしてみようか、っていう方はぜひ挑戦してみましょう。
Accessの使い方の幅がさらに広がるでしょう。
Acccess関数シリーズは、いったん今回で終了です。
ここまで学習できた方は、Access関数の基礎を身に付けることができたと言えます。
他にもいろいろなAccess関数がありますが、このシリーズで学習した関数の基本を活かすことで、きっと自力で使いこなせるようになるでしょう。
本日もありがとうございました。