【SQL】Accessのsqlを使って、条件ごとの値の合計を出したいです。

Question

AccessのSQLにて、特定の条件ごとの値の合計および平均を出したいと思っています。
具体的には、下記Table1の'id'ごとに'flag'の値が'test1'と'test2'に条件を分けてその合計を出し、最後に全てのテストの平均を算出したいです。

Table1

id	testscore	flag
A	2	test1
A	3	test2
A	4	test1
A	2	test1
B	3	test2
B	2	test1
B	4	test2

出力したい結果
id|test1score|test2score|avgscore
|:--|:--:|--:|
A|8 |3 |2.75
B|2 |7 |3

以下のようなSQL文を考えており、test1scoreは取得できるのですが、続けてtest2scoreおよびavgscoreを出したい場合はどのようにすればよろしいのでしょうか。
わかる方がいらっしゃいましたら、ぜひ教えていただけたら幸いです。
よろしくお願いいたします。

SELECT id, SUM(testscore) as test1score, SUM(testscore) as test2score, AVG(testscore) as avgscore
 WHERE Table1.flag is 'test1'
FROM Table1
ORDER BY id

Comments

[sazi]

やりたいことはクロス集計クエリーで出来ますが、その結果をどのように使用するんでしょうか？
flagが可変なら、カラムも増減しますので、データシートビューなら問題ありませんが、フォームやレポートの場合は上限を決めておくなどが必要になります。
flagの取りうる範囲が決まっているなら、固定しておく方法もありますので。

Answer 1

クロス集計クエリを使うといいでしょう。

TRANSFORM Sum(Table1.testscore) AS Sumscore
SELECT Table1.id, Avg(Table1.testscore) AS avgscore
FROM Table1
GROUP BY Table1.id
PIVOT Table1.flag;

結果

id	avgscore	test1	test2
A	2.75	8	3
B	3	2	7