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ベストアンサー
- 回答日時:
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)最終的に表示されるデータは2個の要素の結合です。
主となるものはテストMで、別名としてAを割り当てています。
従となる方は(SELECT B.ID ~ BY B.ID)までのクエリです。
同様に別名Xを割り当てています。 SELECTの後に続く、A.IDとは
「Aに属する方のID」と言う意味です。
最初(左)に書いた方が主、後(右)に書いた方が従なので、結合
する場合の記述語は"LEFT JOIN"となります。
(2)従となるクエリも2個の要素から成り立ちます。
主となる方はテストMで、別名Bを割り当てています。()内なので、
Aを使っても良いのですが、外側の別名と紛らわしいので、異なる
別名を使っています。"WHERE B.フラグ=1"でフラグが1のデータのみ
抽出しています。
従となる方もテストMで、結合する条件(ON 以降)は以下の通り。
●主のIDよりも大きい(後方にある)
●主の平均点より大きい点数
但し、これですと、主1に対し、従が複数になる可能性があります。
そこで、主のID1個に絞り(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
- 回答日時:
演算: IIf([フラグ]=1,DMin("ID","テストM","点数>" & [平均点] & " AND ID > " & [ID])-[ID],Null)
「フラグ」が数値型の場合です。
bonaron 様
教えて頂いた内容を演算コントロールに持たせたところ、
一発で思いどうりの動作を致しました。
本当にありがとうございました。
nadyu
No.2
- 回答日時:
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
- 回答日時:
いきなり実際の計算式を出されても困ります。
「演算」の所にはどういう理論で求まる値を出力すべきか、と言う
仕様が提示されていません。そういう話は質問者さんしか知らない
ことなので、どんなオーソリティをもってしても回答が難しい。
他にも、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
以上、内容説明が長くなってしまい申し訳ございません。
どうぞよろしくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 統計学 テストの順位についてです 今回のテストの順位を知りたいのですが、できますでしょうか?今回は僕の学年は 4 2022/11/30 19:15
- Access(アクセス) アクセス テーブルの空白を変数に置換するボタンが作りたい 4 2022/07/08 11:19
- PHP 空文字 "" ですが 空文字の意味を教えてください。 3 2022/08/05 03:51
- その他(悩み相談・人生相談) 中3です。今日テストが帰ってきたんですが 国語39 平均44 英語54 平均58でした。明日他の教科 6 2022/08/31 12:08
- 中学校 現中3です。塾のテストで国語だけ他の4教科より全然できません。理科と数学は平均を結構上回っていて、英 1 2022/09/19 20:40
- 大学受験 推薦入試について教えていただきたいことがあります。 私は、この春高校三年生になります。進路について考 1 2022/04/05 02:04
- 中学校 理科で点数をあげる方法。 4 2022/06/28 23:54
- その他(データベース) Accessのクエリで1フィールドの抽出条件設定をNullでなく全角半角含む空白のみの文字列でない文 1 2023/04/24 15:20
- 中学校 期末試験について 2 2022/06/06 10:26
- C言語・C++・C# このプログラミングの問題を教えてほしいです。 キーボードからデータ数nとn個のデータを入力し、平均値 3 2022/12/19 22:51
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
フラグをたてるってどういうこ...
-
下4桁を*表示
-
SQLで一定時間周期毎の抽出方法...
-
内部結合
-
SELECT INTOで一度に複数の変数...
-
【SQL】他テーブルに含まれる値...
-
Accessで今日から5日後
-
truncate tableを使って複数の...
-
テーブル名が可変の場合のクエ...
-
結合したテーブルをSUMしたい
-
オラクルのUPDATEで複数テーブル
-
ACCESSのVBAにてExcelに行...
-
sqlに記述できない文字
-
UPDATEで既存のレコードに文字...
-
PostgreSQLのtimestamp型で時間...
-
SQLで、Join句で結合したテ...
-
全テーブルの列数を調査したい
-
データ無し時は空白行にしたい...
-
集計でテストの各教科の最高得...
-
ADO+ODBCでテーブルに接続する...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
フラグをたてるってどういうこ...
-
SQLで一定時間周期毎の抽出方法...
-
下4桁を*表示
-
Excel 複数のセルが共に一致す...
-
カラムに設定した日付が24時間...
-
フラグの文字型は?
-
【sqlite3】deleteしても.dbフ...
-
【エクセル/excel】if関数とフラグ
-
selectによるレコード取得結果...
-
ディスクデフラグについて
-
SQLの質問
-
access vba での left join に...
-
Accessのクエリ抽出
-
SELECT INTOで一度に複数の変数...
-
【SQL】他テーブルに含まれる値...
-
sqlに記述できない文字
-
UPDATEで既存のレコードに文字...
-
テーブル名が可変の場合のクエ...
-
エラーを起こす方法
-
オラクルのUPDATEで複数テーブル
おすすめ情報