＜Access いろいろとクエリーでグループ集計をしてみる＞
どうも、三流プログラマーのＫｅｎ３です。 最近、質問もらうけど、 なかなか、解答できてない三流プログラマーのＫｅｎ３です。 ※私のレベルで手に余る高度な質問が多くて。 今回の、サンプルファイルは、 http://www.ken3.org/vba/lzh/vba129.lzh にdb129.mdb(Access2000版)が保存されています。 ※クエリーをいじって、遊んでみてください。/* * １. 今回のキッカケ */
郵便番号の集計システムを現在やってます。 前回、ExcelのシートをAccessにインポートしました。 今回は、データを集計してみたいと思います。 集計方法（集計ルール） ^^^^^^^^^^^^^^^^^^^^^^ In message "[VBAで楽しく No.127] - 要求を聞き、疑問点をつぶし、仕様書を書く", >2.集計処理 > 郵便番号をカウントする > 郵便番号が７桁そろっているものを集計する > 同様に、３桁しかないもの,２桁しかないもの,その他 > を集計する。 > 集計結果が１０以下（１〜９）のデータは、最後に△で集計する。 --- なんて、ルールより、ほしい結果を見た方がわかり易いかな。 （※だったら、このわかりにくい設計書は失敗ってことでは？） シート１（７桁正しいデータ） 郵便番号 集計数 000-0001 20 000-0002 19 000-0003 18 000-0004 11 シート２（７桁以外のデータ） 郵便番号 集計数 郵便番号 集計数 561 15 98 13 562 16 97 56 ←郵便番号が２桁しかないもの、なおかつ 563 20 78 35 集計数が１０以上のものを番号順に並べる 564 36 565 29 2 15 ←郵便番号が１桁しかないもの、なおかつ 566 39 3 20 集計数が１０以上のものを番号順に並べる 567 11 568 22 △△△ 1045 569 44 ↑ 郵便番号の集計で１〜９までのモノがあれば 570 33 △△△として郵便番号が何桁であろうが 571 55 この部分に計上することになる。 572 66 ↑ 郵便番号が3桁しかないもの、なおかつ 集計数が１０以上のものを番号順に並べる/* * ２．単純に集計を行う */
早い話が、インポートされた、 MOTO_DATA テーブルを同じ郵便番号で集計して（数を数えて） それぞれの条件で扱いやすければいいって感じで。 MOTO_DATAテーブル内のテストデータ 郵便番号 氏名 住所 電話番号 194-0012 三流 東京都町田市 070-9999-8888 228 タコスケ 神奈川県相模原市 070-9999-8889 220-0021 金城 神奈川県横浜市西区 070-9999-8890 220-0021 内川 神奈川県横浜市西区 070-9999-8891 220-0021 鈴木尚 神奈川県横浜市西区 070-9999-8892 ・ ・ ・ まずは、単純に集計してみます。 クエリーで、MOTO_DATAを選択後、郵便番号を選び、 メニューの表示を選択、集計にして、 グループ化とカウントを入れます
↑操作イメージ
すると、郵便番号でカウントされた値が集計されたクエリーができます。
郵便番号 郵便番号のカウント
194-0012 1
220-0021 14
228 1
232 11
700-0941 1
↑クエリー結果イメージ
/* * ３．文字数を数えてみる(演算フィールドの追加) */
結果が、 郵便番号 郵便番号のカウント 194-0012 1 220-0021 14 228 1 232 11 700-0941 1 と、なったけど、このままでは、使いにくいです。 194-0012 1 220-0021 14 700-0941 1 と 228 1 232 11 の２つに分けたいんだけど、何か、方法は無いのか・・・ 当たり前の話、文字数が違うので、Len関数を使って、文字数を求めてみます。 桁数:Len([郵便番号])とクエリーの演算フィールドを作成してみます。 フィールドに桁数:Len([郵便番号]) 集計を演算にしたフィールドを作成します。
↑演算フィールド作成イメージ
このクエリーを実行すると、
郵便番号 郵便番号のカウント 桁数
194-0012 1 8
220-0021 14 8
228 1 3
232 11 3
700-0941 1 8
上記のような結果が得られます。
↑実行結果イメージ
/* * ４．大きく３つのパターンに分ける */
郵便番号 郵便番号のカウント 桁数 194-0012 1 8 220-0021 14 8 228 1 3 232 11 3 700-0941 1 8 ここまで、なんとか、クエリーでもってこれました。 これを パターン１ 郵便番号７桁（−付で８桁）、カウント１０以上 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 郵便番号 郵便番号のカウント 桁数 220-0021 14 8 パターン２ 郵便番号７桁以外（−付で８桁以外）、カウント１０以上 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 郵便番号 郵便番号のカウント 桁数 232 11 3 パターン３ カウント数が１０以下（１〜９）※郵便番号が何桁であろうが ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 郵便番号 郵便番号のカウント 桁数 194-0012 1 8 228 1 3 700-0941 1 8 に分けてみたいですね。 元のクエリーに条件を付けて、 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ３つのクエリーを作成してみたいと思います。 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ パターン１ Q_YUBIN_7 郵便番号７桁（−付で８桁）、カウント１０以上 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 郵便番号 郵便番号のカウント 桁数 220-0021 14 8 --- 条件は >=10(10以上) で =8(桁数は8桁)
↑設定・実行結果イメージ
パターン２ Q_YUBIN_ETC
郵便番号７桁以外（−付で８桁以外）、カウント１０以上
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
郵便番号 郵便番号のカウント 桁数
232 11 3
---
条件は >=10(10以上) で <>8(桁数はNot 8桁)
↑設定・実行結果イメージ
パターン３ Q_YUBIN_1to9
カウント数が１０以下（１〜９）郵便番号が何桁であろうが
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
郵便番号 郵便番号のカウント 桁数
194-0012 1 8
228 1 3
700-0941 1 8
---
条件は <10 (10以下) で (桁数の条件は無し)
↑設定・実行結果イメージ