Access2003 条件付きの平均について

質問

Access2003　条件付きの平均について

以下のテーブルがあるとします

生徒　　　　年　　　月日　　国語　　英語　　数学
Ａくん　　2008年 　4/10　　　30　　　42　　　50
Ａくん　　2008年　 4/22　　　24　　　36　　　48
Ａくん　　2008年　 5/11　　　10　　　80　　　20
Ａくん　　2008年　 5/17　　　16　　　70　　　60
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
Ａくん　　2009年 　4/12　　　38　　　39　　　82
Ａくん　　2009年　 5/11　　　10　　　80　　　20
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・

2008年はテストを月２回、2009年は月１回実施しています。
これを、クエリにより、2008年は月２回の平均値（日付は１回目の日）、
2009年はそのまま１回分の値に表すことは可能でしょうか？

生徒　　　　年　　　月日　　国語　　英語　　数学
Ａくん　　2008年 　4/11　　　27　　　39　　　49
Ａくん　　2008年　 5/11　　　13　　　75　　　40
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・
Ａくん　　2009年 　4/12　　　38　　　39　　　82
Ａくん　　2009年　 5/11　　　10　　　80　　　20
　　　　　　　　　　・
　　　　　　　　　　・
　　　　　　　　　　・

※生徒は、Ａくんのほかにも複数いることを想定しています。

特定年のみ、その月の平均値を表示する方法が判らず困っております。
以上、初心者な者で恐縮ですが、ご教示をお願いいたします。

回答No.2

DexMachina · ベストアンサー

> 特定年のみ、その月の平均値を表示する方法が判らず困っております。

ご質問文の例で、月1回の2009年は「1回の平均」と考えればよいので、
単純に「月ごとの平均」を出せばよいことになります。

ですので、残る問題は、現状の記録内容から、どうやって「月ごと」という
グループ化を行うか、になります。

とりあえず、『月日』がテキスト型であれば、添付画像のようなクエリで、
平均を求めることができます。
(テーブル名は、仮に『成績』としました)

なお、添付画像では、肝心の「月ごとのグループ化」を行っている部分の
式が「Grp: [年] & 」で切れていますが、式の全容は以下の通りです：

Grp: [年] & Left([月日], InStr(1, [月日], "/") - 1)

※この列は表示不要なので、『表示』のチェックは外しておきます。
　(クエリを保存して閉じると、「Grp:」のみ削除され、式だけが残ります)
※Left関数は、第1引数に指定した文字列(上では「月日」フィールドの
　値)から、第2引数で指定した文字数を返す関数です。
　InStr関数は、第2引数で指定した文字列から、第3引数で指定した
　文字列を検索し、その位置を返します。
　(第1引数は、検索の開始位置)
　これにより、『月日』のデータから「/」の位置を確認し、その1つ前(=-1)
　までの文字列を『月』として取得しています。

◆『月日』のデータ型が「日付/時刻型」で、書式設定で月日だけを表示
　させていた場合は、上記では対応できません。
　また、表示させる『月日』はクエリで「最小」を指定していますが、実際の
　データが「3/7」と「3/15」だった場合は「3/15」が表示されます。
　「3月7日」の方を表示させるには、一桁の日付を「3/07」と記録しておく
　必要があります。

回答No.3

piroin654

Accessでの日付の型に関することは、ヘルプあるいは
以下のサポートに掲載されています。

http://support.microsoft.com/kb/210276/ja

また、AccessとExcelでの日付型の違いは
ここで述べるより以下のサイトに詳しく
かいてあります。参考にしてください。

http://www.f3.dion.ne.jp/~element/msaccess/AcTipsExcelLinkDataType.html

回答No.1

piroin654

日時の管理に関してとやかく言うつもりは
ありませんが、データベースではこのような
日時の管理は統計、分析において無駄な
工程を追加したり分析、統計が困難に
なったりします。
もし、AccessとExcelでデータのやり取りを
するなら、一番元になるデータはAccessに
入力し、必要があればExcelにエクスポート
し、閲覧、分析をするようにすれば後々の
データの管理、統計、分析が楽です。

質問のことですが、クエリを二つ作ります。
まず、提示されたテーブルをT成績とします。
一つはQ日時変換、もう一つは適当な名前で
結構ですが、Q成績統計とします。

Q日時変換
SELECT CDate(Format(CDate(Left([T成績]![年],4) & "/" & ([T成績]![月日])),"yyyy\,mm\,dd")) AS 日時, T成績.名前, T成績.国語, T成績.英語, T成績.数学
FROM T成績;

Q成績統計
SELECT DISTINCTROW Format$([Q日時変換].[日時],'yyyy/mm') AS 試験実施年月, Q日時変換.名前, Avg(Q日時変換.国語) AS 国語の平均, Avg(Q日時変換.英語) AS 英語の平均, Avg(Q日時変換.数学) AS 数学の平均
FROM Q日時変換
GROUP BY Format$([Q日時変換].[日時],'yyyy/mm'), Q日時変換.名前, Year([Q日時変換].[日時])*12+DatePart('m',[Q日時変換].[日時])-1;

これらのSQL文を新規のクエリのSQLビューに
貼り付け、それぞれの名前で登録してください。
Q成績統計を開くとデータが表示されます。
何かあれば書き込みを。