More than 1 year has passed since last update.

Microsoft Access 成績表などで使う横に集計結果(Sum Avgなど)を表示するSQL -クエリ

Posted at 2022-11-13

設定

目標

国語と英語の2教科の試験の結果を集計して分析するため、偏差値を求める。
このとき、平均点や偏差値を各個人の横に表示したい。

Table名

T_Master

テーブルの中身

こちらが成績表をイメージしたテーブル。といってもごく単純化したものだ。

ID	氏名	英語	国語
1	あ	30	50
2	い	80	70
3	う	75	80
4	え	100	100
5	お	40	65

データ型

CREATE TABLE T_Master(ID Counter Primary Key, 氏名 Text(2), 英語 Long, 国語 Long);

基本的なことだが

Microsoft Accessのクエリの正体はフィールドの書式等のプロパティが付加されたSQLである。
基本はSQLであるが、フィールドの書式の定義、詳細が付加されている。

VBA

注意点

実際は、すべてのテーブルクエリを閉じるを行ってから実行する。

テーブルの削除はエラー表示なしで削除する

On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL "Drop Table T_Master;", True
DoCmd.SetWarnings True
On Error GoTo 0

実際は危ないので、あまり使用しないほうが良い。

コード

MakeTable

Sub MakeTable()
Dim cDB As DAO.Database: Set cDB = CurrentDb
Dim tdf As TableDef
Dim sSQL As String
Dim dRS As DAO.Recordset
Dim Ar1, Ar2, Ar3, iAr As Long, iFld1 As Long
' T_Master削除
On Error Resume Next
DoCmd.SetWarnings False
DoCmd.RunSQL "Drop Table T_Master;", True
DoCmd.SetWarnings True
On Error GoTo 0

Ar1 = Split("あ い う え お", " ")
Ar2 = Split("30 80 75 100 40", " ")
Ar3 = Split("50 70 80 100 65", " ")
sSQL = "CREATE TABLE T_Master(ID Counter Primary Key, 氏名 Text(2), 英語 Long , 国語 Long);"
'cDB.Execute sSQL, dbFailOnError
DoCmd.RunSQL sSQL, True
Set dRS = cDB.OpenRecordset("T_Master", dbOpenDynaset)
dRS.AddNew
iAr = 0
For iAr = 0 To UBound(Ar1)
  With dRS
  .AddNew
  .Fields(1) = Ar1(iAr)
  .Fields(2) = Ar2(iAr)
  .Fields(3) = Ar3(iAr)
  .Update
 End With
Next iAr
dRS.Close
End Sub

集計

英語の平均、国語の平均、教科ごとの平均点、偏差値を計算する
偏差値はIntで切り捨で整数位にしているが、実際はあと一桁必要だろう。
10倍して0.5を足してINTして10で割れば良い。
ただ、ご覧の通りこれだけでもこんなに長いSQLになってしまう。

SELECT T_Master.ID, T_Master.氏名, T_Master.英語, T_Master.国語, [英語]+[国語] AS 総合点, Int((Select Avg([英語]) from T_Master)*100+0.5)/100 AS 英語平均点, Int((Select Avg([国語]) from T_Master)*100+0.5)/100 AS 国語平均点, Int(50+10*([英語]-(select avg([英語]) from T_Master))/(select stdev([英語]) from T_Master)) AS 英語偏差値, Int(50+10*([国語]-(select avg([国語]) from T_Master))/(select stdev([国語]) from T_Master)) AS 国語偏差値, Int(50+10*(([英語]+[国語])-(select avg([英語]+[国語]) from T_Master))/(select stdev([英語]+[国語]) from T_Master)) AS 個人偏差値
FROM T_Master;

Nullが入ってもカウントする

ID	氏名	英語	国語
1	あ	30	50
2	い	80	70
3	う	75	80
4	え	100	100
5	お	40	65
6	か		10

「か」という人は英語が何らかの事情で無効、つまりそもそも受けていないので空白(Empty)となっている。
ここで平均点とは英語の試験を受けた人、つまり5名でカウントする。ここは考え方は色々あると思われるが、デフォルトの仕様はそうなっている。
実は、自動的にクエリはこれを判定する。
これは
(Select Count([英語]) From [T_Master]) AS 英語受験者
このようなフィールドをクエリに設けると、5名とカウントされていることからわかる。
実際のテーブル形式みてみると、

このように個人計、英語偏差値、個人偏差値は空白になる。

選択クエリで絞り込んでも集計（合計等）の結果に反映しない

SELECT T_Master.ID, T_Master.氏名, T_Master.英語, T_Master.国語, [英語]+[国語] AS 総合点, Int((Select  Avg([英語])  from T_Master)*100+0.5)/100 AS 英語平均点, Int((Select  Avg([国語])  from T_Master)*100+0.5)/100 AS 国語平均点, Int(50+10*([英語]-(select avg([英語]) from T_Master))/(select stdev([英語]) from T_Master)) AS 英語偏差値, Int(50+10*([国語]-(select avg([国語]) from T_Master))/(select stdev([国語]) from T_Master)) AS 国語偏差値, Int(50+10*(([英語]+[国語])-(select avg([英語]+[国語]) from T_Master))/(select stdev([英語]+[国語]) from T_Master)) AS 個人偏差値, (Select Count([英語]) From [T_Master]) AS 英語受験者
FROM T_Master
WHERE (((T_Master.ID)<=3));

このようにIDを1~3にしても合計は変わらない。この場合、サブクエリもWHERE (((T_Master.ID)<=3))が必要となる。
デザインクエリで
英語平均点: Int((Select Avg([英語]) from T_Master Where [ID]<=3)*100+0.5)/100
式ビルダーあるいはズームで開いて入力する。デザインビューで入力はできないことはないが、偏差値は2箇所入力しなければ正しい値とならないため、これだけ複雑な式になると入力が困難である。

SELECT T_Master.ID, T_Master.氏名, T_Master.英語, T_Master.国語, [英語]+[国語] AS 総合点, Int((Select  Avg([英語])  from T_Master Where [ID]<=3)*100+0.5)/100 AS 英語平均点, Int((Select  Avg([国語])  from T_Master Where [ID]<=3)*100+0.5)/100 AS 国語平均点, Int(50+10*([英語]-(select avg([英語]) from T_Master Where [ID]<=3))/(select stdev([英語]) from T_Master Where [ID]<=3)) AS 英語偏差値, Int(50+10*([国語]-(select avg([国語]) from T_Master Where [ID]<=3))/(select stdev([国語]) from T_Master Where [ID]<=3)) AS 国語偏差値, Int(50+10*(([英語]+[国語])-(select avg([英語]+[国語] ) from T_Master Where [ID]<=3))/(select stdev([英語]+[国語]) from T_Master Where [ID]<=3)) AS 個人偏差値, (Select Count([英語]) From [T_Master] Where [ID]<=3) AS 英語受験者
FROM T_Master
WHERE (((T_Master.ID)<=3));

このようにただでさえわけがわからないのに、さらにわけがわからない状態となる。
デザイビューがありがたいと思うのはこういうときだろう。

今回のポイント

フィールドを横に集計するのは[フィールド名] + [フィールド名] でよい。数値の四則演算も同様。（日付は別）
すべてのレコードの合計、平均、カウントをを横に示すにはサブクエリを使う
合計を横に示すにはSQL(Select Sum([国語]) from T_Master As 国語計)'、デザインビュー国語計:(Select Sum([国語]) from T_Master)`と入力する。これをサブクエリという。サブクエリは必ずカッコで囲む。
合計、平均、カウント、標準偏差などこの方法で多くの結果を横に表示させることができる。
デフォルトのクエリはNullがあれば除外して集計する。
Selectをさらに、ID1~ID2、英語の上位10人といった絞り方をしても集計結果のフィールドは変わらない。（良くも悪くも）
絞り込んだ結果で集計したい場合は、サブクエリにも同じWhereを追加しなければならない。
このため動的な絞り込みをしている場合には横集計は向かない。全員を集計するか、上位10人だけなどのクエリを最初から作っておいて、そこで使用する。そうしないとSQLをチェックしないと正しい結果が得られるかわからない。
実務上はクエリのフィールドに書式設定をすること。

参考

サブクエリ(その1) - もう一度学ぶMS-Access
通常は1教科ではなく複数の教科を扱うことから、今回はここから発展させた。
今回の件をもう一弾抽象化すると、こうなる。
縦持ちデータを横持ちデータに変換する - もう一度学ぶMS-Access。
Excelでは横に合計を示すことは何でもないが、Accessではちょっとした技術が必要となる。
クエリで合計・平均・最小・最大・カウント・標準偏差・分散を求める
通常は集計行を設ける。しかしこの方法は多数のレコードがある場合、常に最終行にしか表示されない。
横に合計がある場合、下までいかなくても表示される。

クエリの実行

QueryDef.Execute メソッド (DAO)

Database.Execute メソッド (DAO)

構文

Database.Execute(Query,Options)
QueryDef.Execute(Options)

相違点

公式の解説は同じ内容だが、いくつか違う。

QueryDef.Exesuteはクエリを直に指定して実行するか、クエリ名を指定して実行するかが違う。
Databaseはクエリ名がなくても実行できる。つまりクエリが存在しなくてもSQLが実行できる。逆にQueryDef.Executeはクエリがない限り実行できない。
なお、速度としてはQueryDef.Executeが早い。
QueryDefはqdf.Parameters("Organization").Value = "Microsoft"のようにパラメータクエリが実行できる。

Optionsには、次のRecordsetOptionEnum 定数を使用できます。
dbFailOnError など

メモ

Microsoft Access 2013 では、ODBCDirect ワークスペースはサポートされていません。Microsoft Access データベースエンジンを使用しないで外部データソースにアクセスする場合は、ADO を使用してください。
Microsoft Access ワークスペースでは、指定した SQL ステートメントの構文が正しく、ユーザーが適切な権限を持っている場合、1 行も変更または削除できなくても、 Execute メソッドが失敗することはありません。したがって、 Execute メソッドを使って更新または削除のクエリを実行するときは、必ず dbFailOnError オプションを指定してください。このオプションを使用すると、影響を受けるレコードの一部がロックされているために更新または削除できない場合、実行時エラーが生成され、既に成功している変更もすべてロールバックされます。

以前のバージョンの Microsoft Jet データベースエンジンでは、暗黙のトランザクション内に自動的に SQL ステートメントが埋め込まれていました。 dbFailOnError を指定して実行したステートメントの一部が失敗した場合は、ステートメント全体がロールバックされました。パフォーマンスを向上するために、バージョン 3.5 以降では、この暗黙のトランザクションが取り除かれました。古いバージョンの DAO コードを更新する場合は、 Execute ステートメントの前後で明示的なトランザクションを使用することを検討してください。

RunSQL マクロアクション

RunSQLはマクロアクションのフィルター、クエリ、検索に位置している。つまり本来のVBAではなく、VBAのもとの簡易マクロの命令。基本的にVBAではDoCmdで書ける（例外あり）
簡易なためか、255バイトしかクエリが書けない。ついでにVBEではユニコードはそのまま書くことができない。
このアクションは、データベースが信頼されていない場合には許可されない。

Transaction

デフォルトでトランザクションがTrueだがDoCmd.SetWarnings Falseで無効化される。
以下の例では問い合わせすることなく追加される。RunSQLがTrueでも同様。

Sub test()
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into T_Master ([氏名],[英語],[国語]) Values('き','70','80');", False
DoCmd.SetWarnings True
End Sub

またBegeinTransが効かない。

Sub test()
BeginTrans
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into T_Master ([氏名],[英語],[国語]) Values('き','70','80');", True
DoCmd.SetWarnings True
Rollback
End Sub

本来はRollbackが効いてレコードは追加されないはずだが、この場合、確認なしで追加される。

「RunSQL」と「Execute」の違い

2009年頃まではRunSQLを使うことが多かった。

私自身は、「マクロをVBAに変換」でVBAに移ってきたこともあって、
DoCmd.RunSQLばかり使っていますが・・・(汗)

こちらのサイトが簡潔でわかりやすいかと思いますが、
http://www.mahoroba.ne.jp/~mw_ken/transrunsql.html
Executeを使用した場合はRollbackを使用することで元に戻せる
一方で、RunSQLの場合は、「DoCmd.SetWarnings False」を
予め指定した場合、戻すことができなくなります。
(「True」にした場合も、『更新対象レコード数が表示されたときの
　メッセージで「いいえ」を選択した場合だけ』と非常に限定的)
※「Rollback」については、VBEのヘルプで検索してみて下さい。

その意味では、
　実行中に、(想定済みの)エラーが発生したらRollbackを行う
　　→Executeが必須
　SQL実行後のコードでエラーしても元に戻す必要がない
　(または(私のように)そこまでの制御はできないので諦める(汗))
　　→ExecuteでもRunSQLでも可
といったことになるかと思います。
(Executeの方ではメッセージボックスが表示されないのは、Rollbackという
　取り消し手段を持っているから、ということかと)

この回答者のRollbackとDocmdの指摘は正しいが、そもそもDocmd.RunSQLは255バイトという限界があることを多分知らない。また、パラメータークエリはDoCmd.RunSQLで値を入力しなければならない。
2009年当時はこれでも良かったのだと思われる。

この教えてGoo！で参照されたサイト

トランザクションとRunSQLメソッド - システムアート研究所
現在はこちらにある。
http://systemartlaboratory.com/transrunsql.html

トランザクションとRunSQLメソッド

RunSQLメソッドではトランザクションが効きません。トランザクション中ではExecuteメソッドを使う必要があります。

以下のサンプルはトランザクション中に、レコードのないテーブルに一件のレコードを追加し、ロールバックでもとに戻すものですが、RunSQLメソッドを使った場合は元に戻らず、トランザクションが効いていないことがわかります。フィールド:f1を持つテーブル:T1を予め作成してこのプロシージャを実行してください。RunSQLの行をコメントにして、Executeメソッドの行のコメントを外して実行するとロールバックで元に戻ります。

Sub temp() 
  BeginTrans
  DoCmd.RunSQL "INSERT INTO T1 (F1) VALUES(100)"
  ' CurrentDb.Execute "INSERT INTO T1 (F1) VALUES(100)", dbFailOnError
  Rollback MsgBox "F1(DLookup)：" & DLookup("F1", "T1") 
End Sub

検証

RunSQLで追加してみる

Sub test()
'BeginTrans
DoCmd.RunSQL "Insert Into T_Master ([氏名],[英語],[国語]) Values('き','70','80');" '
'Rollback
'MsgBox "error"
End Sub

Sub test2()
BeginTrans
DoCmd.RunSQL "Insert Into T_Master ([氏名],[英語],[国語]) Values('き','70','80');" '
Rollback
End Sub

どちらも追加するかどうか確認メッセージが表示され、キャンセルをクリックすると直前の動作は取り消される。
CurentDb.Executeはどうか

Sub test3()
DBEngine.BeginTrans
CurrentDb.Execute "Insert Into T_Master ([氏名],[英語],[国語]) Values('き','70','80');", dbFailOnError
DBEngine.Rollback
End Sub

この場合は確認メッセージは表示されず動作が取り消される。ここで主張されていることはたしかにいまでも該当している。

ただし、IDは表示されなくてもカウントが進んでいる。つまり動作の取り消しをしてもIDは1つ増えた状態となる。

SQL

CREATE TABLE ステートメント (Microsoft Access SQL)
Insert Into
値はシングルクォーテーションで一つ一つ囲むこと。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

Microsoft Access 成績表などで使う横に集計結果(Sum Avgなど)を表示するSQL -クエリ

設定

目標

Table名

テーブルの中身

データ型

基本的なことだが

VBA

注意点

テーブルの削除はエラー表示なしで削除する

コード

集計

Nullが入ってもカウントする

選択クエリで絞り込んでも集計（合計等）の結果に反映しない

今回のポイント

参考

クエリの実行

QueryDef.Execute メソッド (DAO)

Database.Execute メソッド (DAO)

構文

相違点

メモ

RunSQL マクロアクション

Transaction

「RunSQL」と「Execute」の違い

この教えてGoo！で参照されたサイト

検証

Execute メソッド (ADO Connection)

RecordsetOptionEnum 列挙 (DAO)

DBEngine.BeginTrans メソッド (DAO)

Workspace.BeginTrans メソッド (DAO)

BeginTrans メソッド、CommitTrans メソッド、および RollbackTrans メソッド (ADO)

BeginTrans メソッド、CommitTrans メソッド、RollbackTrans メソッドの使用例 (VB)

Access VBA リファレンス(learn.microsoft.com)

SQL

Microsoft Access 成績表などで使う横に集計結果(Sum Avgなど)を表示するSQL -ク エリ

設定

目標

Table名

テーブルの中身

データ型

基本的なことだが

VBA

注意点

テーブルの削除はエラー表示なしで削除する

コード

集計

Nullが入ってもカウントする

選択クエリで絞り込んでも集計（合計等）の結果に反映しない

今回のポイント

参考

クエリの実行

構文

相違点

メモ

Transaction

この教えてGoo！で参照されたサイト

検証

SQL

Microsoft Access 成績表などで使う横に集計結果(Sum Avgなど)を表示するSQL -クエリ