クロス集計の行合計

質問

いつもお世話になっております。

現在、Access2003でアプリケーションを作成しています。
以下のようなデータがあり

テーブル:科目マスタ
[科目_ID] [科目]
1 数学
2 国語
3 英語

テーブル:トランザクション
[学籍番号] [科目] [点数]

001 1 80
001 2 79
001 3 60
002 1 83
002 2 67
003 3 31

といったデータが存在する場合に
クロス集計を行って

[学籍番号] [合計得点]
001 219
002 181

のようなデータを作成し、
レポートに表示する場合に、平均の数値を計算したい場合は
どのようにすればいいんでしょうか？
他にも全教科合計に対する各教科の割合なども
計算したいと思います。

レポートの詳細の部分で

（txtAvgはテキストボックスコントロール名）
Me("txtAvg").ControlSource = 計算
というふうにしてると、すべて1行目の値が入ってしまうので
困っています。

どなたかご教授、宜しくお願いします。

回答No.2

1050 円（@1050YEN）

SQLのサンプルです。
サンプルを実現するためには、提示されている情報に一部改変を要します。

※変更
--------------------------
>テーブル:トランザクション
>[学籍番号] [科目] [点数]
--------------------------
を一部変更
※[科目]→[科目ID]に変更

さらに#1でvizzarさんが言われている
>2科目しか受験していない学生は2科目の平均
への対応と、全くトランザクションに載っていない学籍番号の持ち主への対応のため、以下のテーブルを追加しております。

※追加
--------------------------
テーブル:学生マスタ
>[学籍番号] [学生氏名]
--------------------------
学籍番号 学生氏名
001 あ
002 い
003 う
004 え
--------------------------

以下にSQL文を張ります。
※注意!!≪全角スペースを利用しておりますので、半角に置換してください≫
------------------------------------------------------------
TRANSFORM Sum(個別点.点数) AS 点数の合計
SELECT 個別点.学籍番号, 個別点.学生氏名, Sum(個別点.点数) AS 合計点数, Avg(個別点.点数) AS 平均点
FROM [
　　　　　　SELECT
　　　　　　　　　　マトリックス.*
　　　　　　　　　　, IIf([トランザクション].[点数] Is Null,0,[トランザクション].[点数]) AS 点数
　　　　　　FROM
　　　　　　　　　　(
　　　　　　　　　　　　SELECT *
　　　　　　　　　　　　FROM
　　　　　　　　　　　　　　　　学生マスタ
　　　　　　　　　　　　　　　　,科目
　　　　　　　　　　) AS マトリックス
　　　　　　　　　　LEFT JOIN
　　　　　　　　　　トランザクション
　　　　　　　　　　ON (マトリックス.学籍番号 = トランザクション.学籍番号)
　　　　　　　　　　AND (マトリックス.科目_ID = トランザクション.科目ID)
　　　　]. AS 個別点
GROUP BY 個別点.学籍番号, 個別点.学生氏名
ORDER BY 個別点.学籍番号, 個別点.科目 DESC
PIVOT 個別点.科目

お礼

回答が遅くなり申し訳ありません。

この方法でも出来そうだったのですが、実際のテーブルはもっと複雑
だったので、一度ワークテーブルのようなものを作成する。
モジュールを作成し、クロス集計後のテーブルを作成することにしました。

ご教授ありがとうございました。

回答No.1

noname#79209

こんなクエリでどうでしょう。

TRANSFORM Sum(トランザクション.点数) AS 点数の合計
SELECT トランザクション.学籍番号, Sum(トランザクション.点数) AS [合計 点数], Avg(トランザクション.点数) AS 平均点
FROM トランザクション LEFT JOIN 科目マスタ ON トランザクション.科目 = 科目マスタ.科目_ID
GROUP BY トランザクション.学籍番号
PIVOT 科目マスタ.科目;

ただし、2科目しか受験していない学生は2科目の平均になってしまいますが．．．

お礼

お返事が遅くなってしまいました。

実際はもっと複雑な計算がしたいので、ちょっと厳しいですね・・・