＜Access クエリー ２つのテーブル結合して集計したい＞
どうも、三流プログラマーのＫｅｎ３です。 今回は、 メールでもらった質問を処理するために、 Accessのクエリーで表を結合して集計してみたいと思います。/* * １．今回のキッカケ */
メールで下記の質問をもらいました ---- >ACCESSでのクエリで分からないことがあります。 > >下記のような表１、表２があった場合どうしても、思うような結合がされません。 >よい方法がないでしょうか。 表１ ID 項目 単位 入り数量 1 春 コ 5 2 夏 コ 6 3 秋 コ 7 4 冬 コ 8 5 冬至 ｍ 5 6 夏至 ｍ 6 7 春分 ｍ 7 8 秋分 ｍ 8 9 冬至 ｍ 1 10 夏至 ｍ 2 11 夏 コ 3 12 秋 コ 4 13 冬 コ 5 14 冬至 ｍ 6 15 冬 コ 7 表２ ID 項目 単位 出力数量 1 春 コ 7 2 春 コ 8 3 春 コ 5 4 夏 コ 6 5 秋 コ 7 6 冬 コ 8 7 春 コ 1 8 春 コ 2 9 春 コ 3 10 春 コ 4 11 夏 コ 5 12 秋 コ 5 13 冬 コ 6 表まとめ 項目 単位 入り数量 出力数量 差（入り−出力） 春 コ 夏 コ 秋 コ 冬 コ 春分 秋分 の部分がどうしても出てこない 冬至 また、数量も集計されたものとなっていない 夏至 ---- 表をつなげて、集計してみるかぁ、、、 いろいろとありそうだけど、やってみますか。/* * ２．結合方法の調査、テスト */
テーブルの結合方法を探ってみます。 えっ、なんで？ただ？線でつなげるだけじゃないの？ 質問で、 春分 秋分 の部分がどうしても出てこない 冬至 また、数量も集計されたものとなっていない 夏至 入りのテーブルにあって、出力のテーブルに無いデータ、 春分 秋分 冬至 夏至 が出てきていないので、結合をまずは、探ってみます。 クエリーでテーブルを２つ表示させ、 まずは、項目でデータをつなげます。
その後、表示・集計を出し
項目をグループ化、単位を先頭、入り数量と出力数量は合計で
クエリーを作成すると、
項目 単位の先頭 入り数量の合計 出力数量の合計
夏 コ 18 22
秋 コ 22 24
春 コ 35 30
冬 コ 40 42
と集計される。
ほんとだ、春分 秋分 冬至 夏至が集計されない。
なんでだろう？ってギャグはやめといて、
両方の項目が一致しないと、結合されないみたいですね。
これを防止したいので、表１の入り数量のテーブルを基準に結合してみます。
指定は簡単で、
^^^^^^^^^^^^^^
結合の線をクリック後、右ボタンを押すと、結合プロパティが表示されます。
２番の、表１の全レコードと表２の同じ結合フィールド
を選択します。
実行すると、
項目 単位の先頭 入り数量の合計 出力数量の合計
夏 コ 18 22
夏至 ｍ 8
秋 コ 22 24
秋分 ｍ 8
春 コ 35 30
春分 ｍ 7
冬 コ 40 42
冬至 ｍ 12
となります。
なんか、集計値がおかしくない？
えっ、どこが？
夏の入り数の合計ってさ、
１８になっているけど、データ見ると、
ID 項目 単位 入り数量
2 夏 コ 6
11 夏 コ 3
で９じゃないの？
同様に、春ってさ３５になっているけど、
ID 項目 単位 入り数量
1 春 コ 5
の５しか、データ無いんだけど（笑）
あらら、ホントだ、なんで？
表１と表２をつなげてグループ化してるけど、
表１と表２って１対１じゃないから、
レコード数がｎ＊ｎ作成されるんですね、
そのレコード群に対してグループ化の集計をかけてるからねぇ。
表２の夏は、
ID 項目 単位 出力数量
4 夏 コ 6
11 夏 コ 5
と、２レコード
表２の春は、
ID 項目 単位 出力数量
1 春 コ 7
2 春 コ 8
3 春 コ 5
7 春 コ 1
8 春 コ 2
9 春 コ 3
10 春 コ 4
と、７レコードあったんですね。
/* * ３．スマートに出来なかったのでドロドロした方法 */
なんだよ、画面コピーしながら説明文作ってたけど、ダメだったってことジャン。 もったいないから、上の２章で意味無い失敗として使ってるけど（笑） さてと、気を取り直して、ドロドロした処理を作成しますか。 直接つなげられないなら（方法あるかもしれないが、今はわからないので） データをキレイにしてから、つなげますか。 キレイにする？ えっと、データ料理の下準備じゃないけど、 先に表１と表２を集計するクエリー集計１と集計２を作成します。 表１のデータ ---- ID 項目 単位 入り数量 1 春 コ 5 2 夏 コ 6 3 秋 コ 7 4 冬 コ 8 5 冬至 ｍ 5 6 夏至 ｍ 6 7 春分 ｍ 7 8 秋分 ｍ 8 9 冬至 ｍ 1 10 夏至 ｍ 2 11 夏 コ 3 12 秋 コ 4 13 冬 コ 5 14 冬至 ｍ 6 15 冬 コ 7 ---- を集計して、下記のようなクエリーを作成する。 集計１ ---- 項目 単位 入り数量の合計 夏 コ 9 夏至 ｍ 8 秋 コ 11 秋分 ｍ 8 春 コ 5 春分 ｍ 7 冬 コ 20 冬至 ｍ 12 ---- と、表１を元に集計を行うクエリーを作成する。
↑作成イメージ
表２のデータ
----
ID 項目 単位 出力数量
1 春 コ 7
2 春 コ 8
3 春 コ 5
4 夏 コ 6
5 秋 コ 7
6 冬 コ 8
7 春 コ 1
8 春 コ 2
9 春 コ 3
10 春 コ 4
11 夏 コ 5
12 秋 コ 5
13 冬 コ 6
----
を集計して、下記のようなクエリーを作成する。
集計２
----
項目 単位 出力数量の合計
夏 コ 11
秋 コ 12
春 コ 30
冬 コ 14
----
と、表２を元に集計を行うクエリーを作成する。
↑作成イメージ
これで、双方、先に集計を行い、データがキレイにまとまってます。
この、集計１と集計２をつなげるクエリー、結合１２を作成します。
テーブル選択でクエリーを選択します。
あとは、項目で結合して、それぞれの項目を表示します。
実行結果は、下記のようになります。
----
項目 単位 入り数量の合計 出力数量の合計
夏 コ 9 11
夏至 ｍ 8
秋 コ 11 12
秋分 ｍ 8
春 コ 5 30
春分 ｍ 7
冬 コ 20 14
冬至 ｍ 12
----
やっと、完成しました。
オイオイ、何か、忘れてない？
えっ？何？
ボケんなよ、
表まとめ
項目 単位 入り数量 出力数量 差（入り−出力）
春 コ
夏 コ
秋 コ
冬 コ
よく見ろよ、差って書いてあるでしょ・・・
あっ、ホントだぁ。
/* * ４．演算フィールドで差を計算する */
差を計算したいので、クエリーのフィールドに演算フィールドを作成します。 差:入り数量の合計 - 出力数量の合計 と空いているフィールドに入力します。(コロンは:と半角でお願いします) 入力すると、 差: [入り数量の合計]-[出力数量の合計] みたいに、［］が付くが、気にしないでね。
↑入力位置
結果
----
項目 単位 入り数量の合計 出力数量の合計 差
夏 コ 9 11 -2
夏至 ｍ 8
秋 コ 11 12 -1
秋分 ｍ 8
春 コ 5 30 -25
春分 ｍ 7
冬 コ 20 14 6
冬至 ｍ 12
----
よし、完成。と思ったが、出力が無いと、差が出てこない（計算されない）
う〜ん、困った。
よし、ＮＺ関数を使用して逃げてみます。
差: [入り数量の合計]-nz([出力数量の合計],0)
と、NZ関数を使用して、ＮＵＬＬの時は０、それ以外は元の値を返すように細工して、
結果
-----
項目 単位 入り数量の合計 出力数量の合計 差
夏 コ 9 11 -2
夏至 ｍ 8 8
秋 コ 11 12 -1
秋分 ｍ 8 8
春 コ 5 30 -25
春分 ｍ 7 7
冬 コ 20 14 6
冬至 ｍ 12 12
----
やっと、完成したのかなぁ。