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

クロス集計クエリで横計、縦計を表示する

タイトルのような質問をたまに掲示板でみかけます。横計は、「クロス集計クエリウィザード」の最後で「集計値を表示する」にチェックを入れると簡単に表示させることができます。縦計は、ちょっと工夫が必要です。

縦計は、フォームやレポートで表示するなら、フォームフッターかレポートフッターのテキストボックスに 集計関数(Sum, Count等)の式を使えば表示できますので、通常はクエリで表示しなければならないことはありません。あるとすると、データシートビューで表示したいとか、縦計も含めてエクスポートしたいというときぐらいしか思いつきませんが、どうしても必要なときもあるかも知れません。

そこで、今回はこれを実現する方法の紹介です。

QryCrossSumColSum1.png

難易度:

仕様

テーブル1
ID担当者商品数量
1Aa1
2Ab2
3Ac3
4Ad2
5Ae1
6Bb3
7Bc4
8Bd2
9Be1
10Bf3
11Ca6
12Cb5
13Cc4
14Cd1
15Ce2
16Cf3
17Da2
18Dc1
19Dd5
20De4

上記のテーブルから下記のクロス集計クエリを作成します。

クエリ1のクロス集計
担当者数量合計abcdef
A912321
B1334213
C21654123
D122154
合計55910121086

ユニオンクエリを利用する方法

普通に思いつくのは、クロス集計クエリと商品でグループ化した集計クエリを作成して、この2つのクエリをユニオンクエリで連結する方法です。ただ、これだとクエリが3つになるし、サブクエリでひとつに纏めることもできません。(クロス集計クエリはユニオンにできない。)

元テーブルをユニオンクエリで連結しておいて、それをクロス集計にすれば2つのクエリで済みますし、サブクエリで一つに纏めることも可能です。

クエリ1

クエリ1のクロス集計

サブクエリを使って一つに纏めると、

連番テーブルとの直積を利用する方法

この方法は、SQLが分からなくても、クエリのデザインビューで作成でき、しかも一つのクエリでできるのがメリットです。

まず、下記のようなテーブルを用意しておきます。

T_Seq
Seq
1
2

クエリの新規作成で、テーブル1 と T_Seq を追加します。2つのテーブルは結合しません。クエリのデザインビューで下図のように設定します。
QryCrossSumColSum.png

SQLでは下記のようになります。

クエリ2

サンプルファイルが下記からダウンロードできます。
QryCrossSumColSum_07.zip (Access 2007-2010 形式 - 113kb)
QryCrossSumColSum.zip (Access 2002-2003 形式 - 109kb)
QryCrossSumColSum_2k.zip (Access 2000 形式 - 108kb)

記事では理解しやすいようにシンプルな例で説明しましたが、サンプルにはそれ以外に、もう少し現実的なデータ、テーブル構成での例も入っています。ご参考に。

追記: 記事では、意味を理解しやすいようにシンプルなテーブル構成で説明したため、正規化されてないテーブル構成になっています。実際は、担当者マスタ、商品マスタなどに分割して、クエリで纏めたものを使う設計になります。その辺りもサンプルMDBをご参考ください。


拍手する

3 Comments

ringojuice says...""

また、助かりました。
いろいろ勉強になりました。

2013.08.08 16:00 | URL | #- [edit]
ringojuice says..."最後の合計欄を除いて、任意の列の並び替え"

hatena様

このクエリについてなんですが、
最後の合計欄を除く並び替えができますか?
個別の列を設定するではなくて、
一発で任意の列を並び替えられる方法はありますか?
恐れ入りますが、よろしくお願い致します。

2013.09.24 17:01 | URL | #mQop/nM. [edit]
hatena says..."re:最後の合計欄を除いて、任意の列の並び替え"

並び替えたいフィールドに並べ替えを設定すればいいでしょう。

集計行が1、それ以外が0になるような演算フィールドを追加しておいて、それと、並べ替えたいフィールドに並べ替えを設定すればいいでしょう。

例えば、

ORDER BY IIf([商品名]="合計",1,0), 並べ替えたいフィールド

というように並べ替えを指定します。

2013.09.24 17:38 | URL | #5uE6dEgY [edit]

Leave a reply






Trackbacks

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