Access演算コントロールについて

質問

Accessの演算コントロール（演算列）で以下を行いたいと思っております。
■条件
(1)フラグの立っている列のみ演算対象。無い場合はnull値を返す。
(2)フラグの立っている列：次回（カレントレコード）以降、
何回目のテストで今回の平均値を上回るかを演算フィールドに返す
(3)テーブル名　テストM

ID 教科　点数　平均点　フラグ　演算
1 国語　　45　　　42　　　0　　　null
2 国語　　40　　　43　　　0　　　null
3 国語　　60　　　80　　　1　　　4　　・・・以降4回目のテストで今回の平均値を上回る
4 国語　　30　　　45　　　0　　　null
5 国語　　55　　　55　　　1　　　1　　・・・次回のテストで今回の平均値を上回る
6 国語　　70　　　47　　　0　　　null
7 国語　　100　 　50　　　0　　　null

Dlookup関数で以下のように式を書きましたが、うまく結果が求められておりません。
=IIf([演算] Is Not Null,DLookUp("[ID]","テストM","[点数] >= " & ([平均点] And "[演算)]" Is Not Null))-[ID],Null)
上記の式で演算を行うとID3のレコード演算結果が「-2」（※1-3の結果）
ID5のレコード演算結果が「-4」（※1-5の結果）になります。
VBAを使ってでも解決したいと思っております。

大変恐縮ですが、どなたかアドバイスでもご教授頂けますでしょうか？
よろしくお願い致します。

回答No.4

nda23 · ベストアンサー

SQLの意味

SELECT A.ID,A.教科,A.点数,A.平均点,A.フラグ,
X.N-A.ID AS 演算
FROM テストM AS A LEFT JOIN
(SELECT B.ID,MIN(C.ID) AS N FROM テストM AS B LEFT JOIN
テストM AS C ON B.ID<C.ID AND B.平均点<C.点数
WHERE B.フラグ=1 GROUP BY B.ID) AS X
ON A.ID=X.ID

(1)最終的に表示されるデータは２個の要素の結合です。
　主となるものはテストMで、別名としてAを割り当てています。
　従となる方は（SELECT B.ID ～ BY B.ID）までのクエリです。
　同様に別名Xを割り当てています。　SELECTの後に続く、A.IDとは
　「Aに属する方のID」と言う意味です。
　最初（左）に書いた方が主、後（右）に書いた方が従なので、結合
　する場合の記述語は"LEFT JOIN"となります。
(2)従となるクエリも２個の要素から成り立ちます。
　主となる方はテストMで、別名Bを割り当てています。()内なので、
　Aを使っても良いのですが、外側の別名と紛らわしいので、異なる
　別名を使っています。"WHERE B.フラグ=1"でフラグが1のデータのみ
　抽出しています。
　従となる方もテストMで、結合する条件（ON 以降）は以下の通り。
　●主のIDよりも大きい（後方にある）
　●主の平均点より大きい点数
　但し、これですと、主１に対し、従が複数になる可能性があります。
　そこで、主のID１個に絞り（GROUP BY B.ID）その従の中で最小の
　ID（MIN(C.ID)）を求めています。この最小値は外側でも使うので、
　フィールド名としてNを割り当てます（AS N)
　もし、該当するデータが無い場合はNULLになります。
(3)主と従を結合します。結合条件は「IDが一致する」なので、
　ON A.ID=X.ID となります。X.Nは(2)で示した次に平均を超える
　点数の中で最小のIDです。X.N-A.IDで目的の値を得ます。
　もし、該当がないと、X.NはNULLになるので、NULLとの演算結果も
　また、NULLになります。

サブクエリ（SELECT B.ID ～ BY B.ID）単独でも実行可能です。
>「抽出条件でデータ型が一致しません」
もしかして、フラグは文字型だからでは？フラグが文字型の場合は
B.フラグ='1' のように引用符（アポストロフィ）で囲まなくては
なりません。（別解の方も同じ修正が必要）

別解の方は一般的でない（Access固有の演算子がある）こと、
全体の結合から抽出するので効率が悪いこと、以上の点から前者に
比べて劣っています（他のやり方もあるという例です）。よって、
忘れて頂いて結構です。

回答No.3

bonaron

演算: IIf([フラグ]=1,DMin("ID","テストM","点数>" & [平均点] & " AND ID > " & [ID])-[ID],Null)

「フラグ」が数値型の場合です。

お礼

bonaron　様

教えて頂いた内容を演算コントロールに持たせたところ、
一発で思いどうりの動作を致しました。
本当にありがとうございました。

nadyu

回答No.2

nda23

SQLの問題でしょう。
(1)フラグ=1のレコードを抽出する
(2)自レコードのIDより大きいIDを持ち、かつ自レコードの平均点＞点数のレコードを求める。
(3)上記(2)で得られたID（複数）のうち、最小のIDを求める。
(4)上記(3)で得られたID-自レコードのIDを「演算」とする。

クエリのSQLビューで下記SQLを記述して実行してみてください。
SELECT A.ID,A.教科,A.点数,A.平均点,A.フラグ,
X.N-A.ID AS 演算
FROM テストM AS A LEFT JOIN
(SELECT B.ID,MIN(C.ID) AS N FROM テストM AS B LEFT JOIN
テストM AS C ON B.ID<C.ID AND B.平均点<C.点数
WHERE B.フラグ=1 GROUP BY B.ID) AS X
ON A.ID=X.ID

別解として
SELECT A.ID,MAX(A.教科) AS 教科,MAX(A.点数) AS 点数,
MAX(A.平均点) AS 平均点, MAX(A.フラグ) AS フラグ,
IIF(MAX(A.フラグ)=1,MIN(B.ID)-A.ID,NULL) AS 演算
FROM テストM AS A LEFT JOIN テストM AS B
ON A.ID<B.ID AND A.平均点<B.点数 GROUP BY A.ID
があります。効率面では前者の方が上なので、参考までにして下さい。

お礼

nda23　様

SQLの知識が必要なんですね。
ご教授頂いた内容は他でも応用が利きそうです。

前項の内容は「抽出条件でデータ型が一致しません」
後項の内容は、エラーは出ませんが、演算フィールドの結果がエラーになってしまいます。

勉強のために、この方法も理解死体のですが・・・。

nba23様よろしければ教えてください！

nadyu

回答No.1

nda23

いきなり実際の計算式を出されても困ります。
「演算」の所にはどういう理論で求まる値を出力すべきか、と言う
仕様が提示されていません。そういう話は質問者さんしか知らない
ことなので、どんなオーソリティをもってしても回答が難しい。
他にも、IDは抜けが無く連続している保証はあるのか？
教科が不連続（例では国語が並ぶが、数学が混ざる場合）だったら
どうするのか？「演算」を得る理論の他にもこういう疑問があります。

補足

nba23様

大変失礼致しました。
以下、補足させて頂きます。

＞他にも、IDは抜けが無く連続している保証はあるのか？
更新クエリで常に連番にするようにしております。
抜けは無いと仮定していただいて問題ございません。

＞教科が不連続（例では国語が並ぶが、数学が混ざる場合）だったら
＞どうするのか？「演算」を得る理論の他にもこういう疑問があります。
サンプルが悪いですね。申し訳ございません。
実は教科フィールドの値は何でも構わないのです。
2009年度1学期中間
2009年度1学期期末
2009年度2学期中間
2009年度2学期期末
2009年度3学期中間
2009年度3学期期末
ある特定の人物の成績を振り返り、フラグの立てたレコードの[平均点]とカレントレコード以降の[点数]を比較したいと思っております。

＞「演算」の所にはどういう理論で求まる値を出力すべきか、と言う
＞仕様が提示されていません。
以下、●の値と▲の値を比較して、▲が上回った時のID番号「7」を求め、現在のID番号「3」を減算して、★「4」という結果を求めたいです。

ID 教科　点数　平均点　フラグ　演算
1 国語　　45　　　42　　　0　　　null
2 国語　　40　　　43　　　0　　　null
3 国語　　60　　　80●　　1　　　4★　・・・以降4回目のテストで今回の平均値を上回る
4 国語　　30▲　　45　　　0　　　null
5 国語　　55▲　　55　　　1　　　1　　・・・次回のテストで今回の平均値を上回る
6 国語　　70▲　　47　　　0　　　null
7 国語　　100▲ 　50　　　0　　　null

以上、内容説明が長くなってしまい申し訳ございません。
どうぞよろしくお願い致します。