Access2003 クロス集計クエリでの合計

質問

☆以下のようなテーブルで、フィールド[ID]～[C]があり、文字および数字のデータが混在しています。
　　↓　　↓　　↓　　↓　　↓　　↓　　↓　　↓
===========================================
（テーブル(1)）
[ID] 　[A] 　 　　　[B] 　　 [C]　　 　
1　　　 111 　　 　英語　　 　10　　　
2　　　 222　 　　 英語　 　　20　　　
3　 　　333 　　 　数学 　　 30　　　
4　　　 111 　　 　国語　　　 40　　
5　　　 222　 　　 数学　 　　50　　　
6　　 　333 　　 　国語　　 　60　　　
7　　　 222　 　　 国語　 　　70　　
8　 　　333 　　 　英語　　 　80　　　　
9　　　 111 　　 　数学　　　 90　　　　
===========================================

[課題]　クロス集計クエリを作成する

(1)　フィールド[A]を行見出しに設定、
　　 フィールド[B]を列見出しに設定、
　　 フィールド[C]を集計する項目に設定する。

(２)　フィールド[C]の値を列ごとに平均を計算し、
　　　行ごとに合計を計算する。
　　※ 列と行で、それぞれ集計した値を表示する
　　↓　　↓　　↓　　↓　　↓　　↓　　↓　　↓
===========================================
[クロス集計結果]
　　　　合計　　英語　　国語　　数学　　　　
111　　140　　　10　　　 40　　　 90　　　 　　　
222　　140　　　20　　　 70　　　 50　　　　
333　　170　　　80　　　 60　　　 30　
－－－－－－－－－－－－－－－
平均　 150　　 36　　　　 56　　　56
===========================================
　　↑　　↑　　↑　　↑　　↑　　↑　　↑　　↑　　　
上記のようなクロス集計クエリの結果を表示させたいのですが、
どうすればよろしいでしょうか？
また、クエリだけでは出来ない場合は、SQLで教えていただければ幸いでございます。

お手数お掛けしますが、何とぞ宜しくお願い致します。

回答No.3

layy · ベストアンサー

クエリでやろうと思えば、

【テーブルからクロス集計】
Ａに対する科目別の合計を出す

______合計__英語__国語__数学
111___140_____10____40____90
222___140____ 20____70____50
333___170_____80____60____30

【テーブルからクロス集計】
Ｂに対するＡごとの合計を出す（軸を逆に考える）

______Ａ’___合計__平均___111__222__333
英語___999___110___110/3___10___20___80
国語___999___170___170/3___40___70___60
数学___999___170___170/3___90___50___30

「Ａ’」列はダミーです。
こうしておくと、「Ａ’」「科目」「合計」か「平均」と３項目あるので、
同じく、クロス集計の作られ方を利用すると、

合計行のためのレコード
_______英語___国語____数学
999_____110____170_____170

または
平均行のためのレコード
_______英語___国語____数学
999___110/3__170/3___170/3

ができあがります。

【テーブルからクロス集計】に追加レコードとしてやれば、
フォームでは、同じレコードソースになります。
他のクエリ等で平均や合計を求めて、
母体のクエリ結果にレコード追加してやる、という手です。
レコード区分を設けて、明細は０１、合計は０２、平均は０３とし並べると
おのずと後ろの方に合計、平均となります。

それか、
他のクエリ等で平均や合計を求めているのだから、
フッタ等でダイレクトにその値を取得するのがよくある例かと思います。
Dlookup関数等使うと、他テーブルのとあるフィールドの値を取得したりできます。
「フォームのフッタに合計を出す」
という観点でさがしてみてはどうでしょうか。

お礼

layy様
大変ご丁寧な回答をくださり　ありがとうございました。
クロス集計を軸を逆に2回実行し、さらにクロス集計で合計と平均のレコードを作成して、追加クエリでレコードを追加して、再度クロス集計ということですね。
希望通りの結果を得ることができましたが、かなり大変な作業でした。
「フォームでフッタに合計を出す」方法は、１から勉強してからやってみます。
いろんなやり方があるのが分かり、大変勉強になりました(*^_^*)

回答No.2

mshr1962

クエリウィザードでクロス集計クエリを選択してください。
行見出し　フィールド[A]
列見出し　フィールド[B]
データ　　フィールド[C]
集計方法　合計
で
A　　　合計　　英語　　国語　　数学　　　　
111　　140　　　10　　　 40　　　 90　　　 　　　
222　　140　　　20　　　 70　　　 50　　　　
333　　170　　　80　　　 60　　　 30　
が出来ます。後はフォームなどで、クロス集計クエリの結果を元にして
フォームフッターで平均値を計算する式を設定すればお望み結果になります。

参照URL
http://www.microsoft.com/japan/office/previous/xp/suminaka/access/sousa/1/sousa1_20_1_honbun.htm

もし、初めから(2)の平均値が出るようにしたい場合は、クロス集計クエリでなく
フォームでピボットテーブルを作成する形になります。

補足

ご回答いただき　ありがとうございます。
クロス集計クエリまではできるのですが、その後の平均値が上手く表示できませんでした。できればクエリで完結できればと思っていたのですが、できないようですね。。。
「後はフォームなどで、クロス集計クエリの結果を元にして
フォームフッターで平均値を計算する式を設定すればお望み結果になります」とは、具体的にどのようにすればよろしいでしょうか？操作方法を教えていただけると助かります。
お手数お掛けしますが　よろしくお願い致します。

回答No.1

layy

同じクロス集計を考えて、
今ある、合計を出している縦軸と横軸を反対にすればできるのでは？。

合計 平均 111 222 333
英語 110 ? 10 20 80
国語 170 ? 40 70 60
数学 170 ? 90 50 30

また、この２つのパターンを用意しておき、
DLOOKUP関数を使うなりすえば相互の結果を取得できると思います。
（最終的に１つのテーブルであれば・・・。）