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.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総数クエリ")

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