Accessクロス集計クエリで

質問

Accessのクロス集計クエリで下記のような商品名・納品週を行見出し、受注状況を列見出しにして集計クエリを作ったのですが、受注状況1
受注状況2の合計を列に増やしたいのですがどうすればよろしいでしょうか。

商品名　納品週　　受注状況1　受注状況2　受注状況3　合計
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
A　　　2007/03/25　　　10　　　　　　15　　　　　　　0　　　　　25
A　　　2007/04/02　　　15　　　　　　12　　　　　　 15　　　　　42
A　　　2007/04/09　　　 0　　　　　　10　　　　　　　7　　　　　17
B　　　2007/03/25　　　11　　　　　　 0　　　　　　 11　　　　　22
B　　　2007/04/02　　　10　　　　　　15　　　　　　　0　　　　　25

回答No.2

venzou · ベストアンサー

＊＊方法その１＊＊

クロス集計の結果を元に、クエリを作り集計する。

SELECT *, nz(受注状況1,0) + nz(受注状況2,0) as 小計
FROM クロス集計;

この方法は簡単ですが、場合によっては不具合が出ます。
例えば、"受注状況1"のレコードが0件だった場合、クロス集計に「受注状況1」というフィールドが存在しなくなるのでエラーが出ます。

データの内容によってエラーが出るのは、好ましくないので、あまりお勧めしませんが、受注状況のデータがある程度決まっているなら、この方法でも良いと思います。

＊＊方法その２＊＊

仮に、主キーが「ID」で「数量」というフィールドを合計するとします。

テーブル名（ID,商品名,納品週,受注状況,数量）

----------集計用クエリ----------
SELECT ID,商品名, 納品週, 受注状況, 数量 FROM テーブル名
UNION
SELECT ID,商品名, 納品週, "小計" AS 受注状況, 数量 FROM テーブル名
WHERE (テーブル名.受注状況="受注状況1") OR (テーブル名.受注状況="受注状況2");
UNION
SELECT ID,商品名, 納品週, "合計" AS 受注状況, 数量 FROM テーブル名;
---------------------------------

----------クロス集計クエリ----------
TRANSFORM Sum(集計用クエリ.数量) AS 数量の合計
SELECT 集計用クエリ.商品名, 集計用クエリ.納品週
FROM 集計用クエリ
GROUP BY 集計用クエリ.商品名, 集計用クエリ.納品週
PIVOT 集計用クエリ.受注状況;
------------------------------------

ユニオンクエリで集計用のクエリを作り、このクエリを基にクロス集計をします。その際、クロス集計の合計は使いません。

こちらの方法は作るのがめんどくさいですが、エラーは出ないと思います。

集計用クエリについて解説：

上のSELECT文は、普通に集計する為のものです。すべてのレコードをそのまま選択します。

真ん中のSELECT文は、小計を計算するためのものです。受注状況が"受注状況1"又は"受注状況2"のレコードのみを選択すします。受注状況には"小計"と入れておきます。

下のSELECT文は、クロス集計の合計が利用できなくなるので、合計を計算するためのものです。すべてのレコードを選択し、受注状況には"合計"と入れておきます。

これらをUNIONで連結し、集計用のクエリとします。

お礼

ありがとうございました。
何とか上記方法で出来ました。
本当にたすかりました。

回答No.1

kakkysan

見当違いならごめんなさい

クエリの新しいフィールドを
小計:[テーブル名]![受注状況1]+[テーブル名]![受注状況2]
として
集計行を：先頭
行列の入れ替えを：行見出し

とすれば受注状況1受注状況2の合計を列に増加できるはずです