Access2000での抽出方法

質問

下記のような検索フォームを使って：

体力テスト結果比較検索フォーム
あなたのID　　　　　　　ｘｘｘｘ
比較対象者の年齢　　ｘｘ才　から　ｘｘ才
比較対象者の性別　　男　OR　女　（オプショングループ）
検索実行ボタン

以下のような画面の表示をしたいと考えています：

　　　　　男性（女性）　あなたの　平均値　　あなたの順位　　　　　
　　　　　ｘｘ才～ｘｘ才 最新　　　 との差　　
　　　　　の平均　　　　のデータ
種目１　ｘｘｘ　ｃｍ　　　YYYｃｍ 　CC(YYY-ｘｘ)　 ZZ番/WW人中
種目２　ｘｘ　　回　　　 YY回　　　 CC(YY-xx)　　 ZZ番/WW人中
種目３　ｘｘ　　回　　　 YY回　　　 CC(YY-xx)　　 ZZ番/WW人中

現時点でできているのは：

体力テスト結果テーブルと年齢クエリからフォームを作成し、そのフォームから
検索フォームにより、男女別にかつ、年齢別にデータを取り出すところまで・・・AAとする
例：　　　女性　23歳から38歳までの人の体力テスト結果
　　　　　　　　　種目１　　種目２　　種目３　
AAAさん（ID）　 120cm　　 9回　　　　38回
BBBさん　　　　145cm　　10回　　　　41回
CCCさん　　　 128cm　 12回　　　 34回

できないでいるのは：

上記　AA　より　
１）各種目の平均を出し、２）「あなた」の最新データを抽出し、３）その差を表示し、
４）各種目別のデータ数をカウントし、その中であなたのデータは何番目かを表示すること
です。

現在、BVAを鋭意勉強中ですが、独力で上記をプログラムできるようなるのが、
いつになるのか見当もつかずご相談する次第です。
ヒントだけでも、結構です。ご教示お願いします。

回答No.6

ARC · ベストアンサー

こちらの方では、#5のバージョンで一応まともに動いてるんで、絶対、どこかに抜け落ちてる点、見落としてる点があると思うんですね。 ちょっとそれを探して見ましょう。

このクエリを実行するには、いくつかの前提条件が必要です。

○体力テスト結果比較1フォームに、以下の名前のテキストボックスがあること。また、そのテキストボックスの内容がきちんと入力されていること。(括弧内は、入力例)
・開始年齢(0)
・最高年齢(100)
・性別選択(-1)(確か、性別の項目は、Yes/No型で、男=Yes, 女=No でしたよね。)
・あなたのID(1)
・あなたの成績(120)(「種目1最新クエリ」を使って、あらかじめ入力しておく必要がある。)
入力内容は全て数字で、単位等が入っていてはいけません。

あと、成績を記録するフィールドですが、文字列形は使わずに、数値型、もしくは日付/時刻型にしておく必要があります。(大小関係を比較する必要があるため)

どうも、性別での絞り込みのあたりが臭そうですので、その辺、修正したクエリを作ってみました。
[性別選択]のオプションボタンは、男が-1,女が0にしておいてください。

SELECT [体力測定最新日].[実施者ID]
FROM 体力測定最新日 INNER JOIN 年齢 ON [体力測定最新日].[実施者ID]=[年齢].[実施者ID]
WHERE [年齢].[年齢] Between Nz([Forms]![体力テスト結果比較1]![開始年齢],0) And Nz([Forms]![体力テスト結果比較1]![最高年齢],200)
And [年齢].[性別]=IIf(Nz([Forms]![体力テスト結果比較1]![性別選択])=-1,Yes,No)
And [体力測定最新日].[実施者ID]<>Nz([Forms]![体力テスト結果比較1]![あなたのID],0)
And [体力測定最新日].[立ち幅跳び]>Nz(DLast("立ち幅跳び","種目1最新クエリ"),0)
GROUP BY [体力測定最新日].[実施者ID];

お礼

ARCさん、大変、ありがとうございました。
お蔭様で、全てクリアになりうまく作動しました。
ご指摘の点で、体力テスト結果比較1フォームの「あなたのID」のプロパティの書式が空欄になっていたので、それを数値にしたこと、及び上記クエリとしたこと、により
解決しました。
また、質問することがあろうかとも思いますが、
その時もどうぞよろしくお願いします。
お礼まで

回答No.5

ARC

>今回のバグの原因として、「一人に対して何件かの体力テスト結果があり得るということが、考慮されていないからではないか」

うーん、少なくとも順位の項目に関しては、これは考慮してたんですが。なんででしょうねぇ。

とりあえず、新たに発見したバグ(「あなたのID」で指定した人物の過去の記録も、順位に含まれてしまう)を修正した上で、もっかい作ってみました。

種目1順位クエリ
SELECT 体力テスト結果.実施者ID
FROM 体力テスト結果 INNER JOIN 年齢 ON 体力テスト結果.実施者ID = 年齢.実施者ID
WHERE 年齢.年齢 Between [Forms]![体力テスト結果比較1]![開始年齢] And [Forms]![体力テスト結果比較1]![最高年齢]
AND 年齢.性別=[Forms]![体力テスト結果比較1]![性別選択]
AND 体力テスト結果.実施者ID<>[Forms]![体力テスト結果比較1]![あなたのID]
AND 体力テスト結果.立ち幅跳び>[Forms]![体力テスト結果比較1]![あなたの成績]
GROUP BY 体力テスト結果.実施者ID;

あと、VBAの
　　![あなたの順位] = DCount("選手ID", "種目1順位クエリ") & "/" & DCount("選手ID", "種目1総数クエリ")
の部分を、
　　![あなたの順位] = DCount("選手ID", "種目1順位クエリ") + 1 & "/" & DCount("選手ID", "種目1総数クエリ")

に変えてみてください。

これでうまくいくかな？(弱気モードなので、自信なし(笑))

補足

ARCさん、お世話様です。

私なりに手を変え品を変え、やってみましたが、どうしても値がゼロになってしまいます。
種目１総数クエリの部分は、問題ありません。その値を仮に７とします。
And (([体力測定結果最新日].[実施者ID])<>([Forms]![体力テスト結果比較1]![あなたのID]))
And [体力測定結果最新日].[立ち幅跳び] > [Forms]![体力テスト結果比較1]![あなたの成績]
下記SQLより上記を切り取ってみると、値は、やはり７（種目１総数クエリと同じ）で問題なし。
And [体力測定結果最新日].[立ち幅跳び] > [Forms]![体力テスト結果比較1]![あなたの成績]
のみを切り取ってみると、値は、６でOK。
また、フォームにおける順位表示も　それぞれ　８／７、７／７（＋１追加済み）となります。下記のままだと値がゼロとなってしまいます。お手上げです。

SELECT [体力測定結果最新日].[実施者ID]
FROM 体力測定結果最新日 INNER JOIN 年齢 ON [体力測定結果最新日].[実施者ID]=[年齢].[実施者ID]
WHERE
((([年齢].[年齢]) Between [Forms]![体力テスト結果比較1]![開始年齢]
And [Forms]![体力テスト結果比較1]![最高年齢]) And (([年齢].[性別])=[Forms]![体力テスト結果比較1]![性別選択]))
And (([体力測定結果最新日].[実施者ID])<>([Forms]![体力テスト結果比較1]![あなたのID]))
And [体力測定結果最新日].[立ち幅跳び] > [Forms]![体力テスト結果比較1]![あなたの成績]
GROUP BY [体力測定結果最新日].[実施者ID];

回答No.4

ARC

どもども、遅くなりましたm(_ _)m
眠い頭でろくすっぽデバッグもせずに書き込むから、こんなことになっちゃうんですね(汗)。

SELECT 体力テスト結果.実施者ID
FROM 体力テスト結果 INNER JOIN 年齢 ON 体力テスト結果.実施者ID = 年齢.実施者ID
WHERE (((年齢.年齢) Between [Forms]![体力テスト結果比較1]![開始年齢] And [Forms]![体力テスト結果比較1]![最高年齢]) AND ((年齢.性別)=[Forms]![体力テスト結果比較1]![性別選択]))
GROUP BY 体力テスト結果.実施者ID;

これでいいはずです。こんどはちゃんと動作確認しました(^^;

補足

テストID　実施者ID　性別　年齢　テスト日　立ち幅跳び
１　　　　　１　　　　女　　26　　9/25　　　175
２　　　　　１　　　　女　　26　　11/26　　 190
３　　　　　１　　　　女　　26　　12/2　　　180
４　　　　　２　　　　男　　29　　11/3　　　189
５　　　　　３　　　　女　　34　　11/6　　　207
６　　　　　４　　　　男　　28　　11/28　　 216
７　　　　　５　　　　女　　40　　11/28　　 225
今回のバグの原因として、「一人に対して何件かの体力テスト結果があり得るということが、考慮されていないからではないか」と気づきました。例えば、女性で20-40歳の人の成績の内、180cmの成績が何番か、調べるにあたっては、
１） 実施者1番のデータを最新のもので絞り込む
―レコード3，4，5，6，7
２） 20-40歳、女性で絞る　　　
　　―レコード 3，5，7
３） 180cmより大きい数字でで絞る　
－レコード　　　5，7
結果は、５人中、3番ということになると思います。
従い、上記１）の絞込み済みクエリ「体力測定結果最新日」（今までは、同一人物に複数のテスト結果があった。）（体力テスト日に対しMAX関数を用いた）を使うことにより解決致しました。ところが、何故か、全ての種目において、「～人中0番」という形になってしまい、頭をひねってみましたが、原因がわかりません。「～人中」の部分はOKなのですが。
よろしくお願いします。

SELECT [体力測定結果最新日].[実施者ID]
FROM 体力測定結果最新日 INNER JOIN 年齢 ON [体力測定結果最新日].[実施者ID]=[年齢].[実施者ID]
WHERE ((([年齢].[年齢]) Between [Forms]![体力テスト結果比較1]![開始年齢] And [Forms]![体力テスト結果比較1]![最高年齢]) And (([年齢].[性別])=[Forms]![体力テスト結果比較1]![性別選択])) And (([体力測定結果最新日].[立ち幅跳び])>([Forms]![体力テスト結果比較1]![あなたの成績]))
GROUP BY [体力測定結果最新日].[実施者ID];

回答No.3

ARC

ありゃ(^^;
まさしくその通りのエラーが出ますね。

えっと、種目1順位クエリの SELECT～の部分を、以下のように書き換えてください。

SELECT 種目1成績.選手ID AS 選手ID

これで、この部分のバグが取れるはずです。

他にも、バグ等ありましたら、補足してやってください。

補足

ARCさん、早速の回答、ありがとうございます。
ご指示どおりやってみたのですが、バグがとれません。
SQL文とそのまま下記に貼り付けました。

体力テスト結果　　　＝　種目１成績テーブル
実施者ID　　　　　　＝　選手ID
年齢　　　　　　　　＝　選手年齢クエリ
体力テスト結果比較1 ＝　検索フォーム
立ち幅跳び　　　　　 ＝　成績

SELECT [体力テスト結果].[実施者ID] AS 実施者ID
FROM 体力テスト結果 INNER JOIN 年齢 ON [体力テスト結果].[実施者ID]=[年齢].[実施者ID]
WHERE ((([年齢].[年齢]) Between [Forms]![体力テスト結果比較1]![開始年齢] And [Forms]![体力テスト結果比較1]![最高年齢]) And (([性別])=[Forms]![体力テスト結果比較1]![性別選択]) And (立ち幅跳び>[Forms]![体力テスト結果比較1]![あなたの成績]))
GROUP BY [実施者ID];

よろしくお願いします。

回答No.2

ARC

このような画面でしたら、XXXとか、YYYの部分をテキストボックスにして、検索フォーム上の検索ボタンをクリックした時に、各テキストボックスに値を代入するっていうのが、お手軽なやり方になるんじゃないかと思います。

流れとしては、こんな感じですね。
1:[結果表示フォーム]と[検索フォーム]を作成する。
2:[結果表示フォーム]の各項目ごとに、値を取得するクエリを作成する
3:[検索フォーム]の[検索ボタン]のクリック時に、それぞれのクエリを実行し、その結果を[結果表示フォーム]の各テキストボックスに代入する。

*-*-*-*-*-*-*-*-*
2:の解説
以下のテーブル、クエリがあって、以下のフィールドを持つものとします。
種目1成績テーブル
　結果ID(オートナンバー型)
　選手ID(数値型)
　成績(数値型)

選手年齢クエリ
　選手ID(数値型)
　年齢
　性別

○平均値を出すクエリ(種目1平均クエリ)
SELECT AVG([成績]) AS 平均成績
FROM 種目1成績 INNER JOIN 選手年齢クエリ ON (種目1結果.選手ID = 選手年齢クエリ.選手ID)
WHERE (年齢 Between [Forms]![検索フォーム]![開始年齢] And [Forms]![検索フォーム]![最高年齢])
　　AND 性別 = [Forms]![検索フォーム]![性別選択] ;

○選手IDで指定した人物の最新のデータ(種目1最新クエリ)
SELECT 成績
FROM 種目1成績
WHERE 選手ID = [Forms]![検索フォーム]![選手ID]
ORDER BY [結果ID];
※このクエリだけでは必要なデータは取り出せない。後で、DLast関数と組み合わせて結果を得る。
※この例では結果IDで並べ替えてるが、計測日時などのデータがあれば、そちらを使ってもOK。

○選手の総数(種目1総数クエリ)
SELECT 選手ID
FROM 種目1成績 INNER JOIN 選手年齢クエリ ON (種目1結果.選手ID = 選手年齢クエリ.選手ID)
WHERE (年齢 Between [Forms]![検索フォーム]![開始年齢] And [Forms]![検索フォーム]![最高年齢])
　　AND 性別 = [Forms]![検索フォーム]![性別選択]
GROUP BY 選手ID;
※このクエリだけでは必要なデータは取り出せない。後で、DCount関数と組み合わせて結果を得る。

○指定した選手の人数ベースでの順位(種目1順位クエリ)
SELECT 選手ID
FROM 種目1成績 INNER JOIN 選手年齢クエリ ON (種目1結果.選手ID = 選手年齢クエリ.選手ID)
WHERE (年齢 Between [Forms]![検索フォーム]![開始年齢] And [Forms]![検索フォーム]![最高年齢])
　　AND 性別 = [Forms]![検索フォーム]![性別選択]
　　AND 成績 > [Forms]![結果表示フォーム]![あなたの成績]
GROUP BY 選手ID;
※このクエリだけでは必要なデータは取り出せない。後で、DCount関数と組み合わせて結果を得る。
※100m走など、「成績が小さい方が偉い」種目の場合は、不等号の向きを逆にすること。

*-*-*-*-*-*-*-*-*
3:の解説
検索ボタンのクリック時に、こんな感じのコードを記述する
Private Sub 検索ボタン_Click()
　With Forms![結果表示フォーム]
　　![種目1平均] = DFirst("平均成績","種目1平均クエリ")
　　![あなたの成績] = DLast("成績","種目1最新クエリ")
　　![平均との差] = ![あなたの成績] - ![種目1平均] & "(" & ![あなたの成績] & "…"
　　![あなたの順位] = DCount("選手ID", "種目1順位クエリ") & "/" & DCount("選手ID", "種目1総数クエリ")
　End With
End Sub

補足

ARCさん
いつも、的を得たわかりやすい回答をいただき深謝いたします。
お蔭様で、後一歩です。というのも、検索ボタンクリック時においてデバックステップインを行ったところ、下記の部分で「指定されたフィールド ‘[選手ID]’　がSQLステートメントのFROM句にある複数のテーブルを参照しました。」というエラー３０７９が出てしまいました。

![あなたの順位] = DCount("選手ID", "種目1順位クエリ") & "/" & DCount("
選手ID", "種目1総数クエリ")

対策をご教示いただければ幸いです。

回答No.1

moisabc

クエリーデザインを開き右クリックするとＳＱＬビューで直接ＳＱＬ文を記述します。
例：ｓｅｌｅｃｔ　ｃｏｕｎｔ（＊）
　　ｆｒｏｍ　ＤＢ名
　　ｗｈｅｒｅ　種目＝　"幅跳び”
で幅跳びの種目のカウントができます。
体力が何番目かを検索するのは
　例：ｓｅｌｅｃｔ　ｃｏｕｎｔ（＊）
　　　ｆｒｏｍ　ＤＢ名
　　　ｗｈｅｒｅ　体力<=あなたの体力　です

参考URL：

http://www.e-taiko.co.jp/

お礼

moisabcさん、回答ありがとうございます。
ただ、小生VBAを含め、まだ、ACCESS知識少なく、上記だけですと検索フォームからの任意の年齢、任意の性別に対し、どう対応したら良いかわかりません。
いづれにせよ、クエリ作成時には非常に参考になりました。どうもありがとございました。