- 成績テーブルから、各生徒の最高点のレコードだけ抽出したい
- 購入履歴テーブルから、各顧客の最新の購入レコードだけ抽出したい
このような場合、DMax関数・DMin関数を使ったり、中間クエリを使うことで目的のデータを抽出することができます。
今回のサンプル
サンプルとして、生徒が3人、それぞれに3科目分（数学・国語・英語）のテスト結果のレコードが存在する、合計9行のデータを用意しました。（IDは生徒を識別する番号）
元のテーブル（テスト結果テーブル）から、それぞれの生徒の最高得点のレコードだけを抽出してみます。（ID=s002の生徒は、数学・英語が同点なので2行抽出されます。）
中間クエリを使う方法
まずは、より簡単な中間クエリを使う方法（クエリを重ねる方法）を説明します。
最初に、各生徒ごとの最高得点を算出するクエリを作成します。
次に、テスト結果テーブルと、先ほど作成したクエリ（生徒ごとの最大得点）を結合すればOKです。
下図のように、IDとID、得点と得点の最大をつなげます。
このクエリを実行すると、期待したとおりのレコードが抽出されました。
DMax関数を使う方法
続いて、DMax関数関数を使う方法です。
長くなるので、先に結論を言うと、今回のケースで生徒ごとの最高得点のレコードだけ抽出するには、得点フィールドの抽出条件を下記のようにすればOKです。
DMax("得点","テスト結果","ID = '" & [ID] & "'")DMax関数について & 練習
DMax関数は、指定した列から最大値を抽出する関数で、オプションとして検索条件を指定することもできます。（なお、DMin関数は最小値を抽出します）
DMax("列名","テーブル・クエリ名","条件式")まずは練習として、簡単な条件で動かしてみます。
生徒は識別せずに、全てのレコードにおける得点の最大値を取得してみます。
最高得点: DMax("得点","テスト結果")
生徒ごとの最高得点を取得する
テーブル全体の最高得点である90点は取得できましたが、本当に欲しい値は生徒ごとの最大値でしたね。式を変更（条件を追加）して、IDごとの最高得点を取得してみます。
最高点: DMax("得点","テスト結果","ID = '" & [ID] & "'")クエリを実行すると、期待どおり生徒ごとの最高得点が取得できました。
"ID = '" & [ID] & "'" の部分はかなりわかりにくいので、例を代入して説明します。
// 仮にs001が検索条件だった場合（s001は文字列なのでシングルクオートで囲む）
DMax("得点","テスト結果","ID = 's001'")
// 本当の検索条件はIDフィールドなので、s001 => [ID]に書き換える
DMax("得点","テスト結果","ID = '[ID]'")
// 上記のままだと、'[ID]'という文字列として検索してしまうので、
// ① ID = ' ② [ID] ③ ' という3つにわけて、& でつなぐ（②以外は文字列なので"で囲む）
DMax("得点","テスト結果","ID = '" & [ID] & "'")最高得点を抽出条件に利用する
生徒ごとの最高得点を取得する式ができ、結果の確認もできたので、得点フィールドの抽出条件に式を移して実行します。
このように、DMax関数を使うことで、最大値をもつレコードを抽出することができました。
補足：DMin関数で最小値をもつレコードを取得する
最小値をもつレコード（≒最も点数が低かった成績データ）をDMin関数で取得できます。
使い方はDMax関数と全く同じです。
DMin("得点","テスト結果","ID = '" & [ID] & "'")
補足：条件をさらに増やしてみる
ここまでは、生徒ごとの最高得点のレコードという条件でしたが、もう少し複雑にして、指定した試験日の生徒ごとの最高得点のレコードを取得してみます。
テスト結果テーブルには、あらたな列（試験日）を追加しました。
まずは、生徒ごとの指定日の最高点を新規フィールドに表示してみます。
なお、指定日はクエリの利用者がyyyy/mm/dd形式で入力できるようにしておきます。
指定日の最高点: DMax("得点","テスト結果","ID = '" & [ID] & "' and 試験日 = #" & [指定日は？（yyyy/mm/dd）] & "#")実行した結果は下図のとおりです。
指定日を2022/11/19にしたパターン（画像上）と、指定日を2022/11/20にしたパターン（画像下）を試してみました。
いずれも、期待したとおり、生徒ごとの指定日の最高点が取得できましたので、この式を得点フィールドの抽出条件として使ってみます。
指定日を2022/11/20として実行してみましが、うまくいっていません。
s002の生徒については、試験日が指定日ではないデータ（2022/11/19）も抽出されています。
得点でしか絞り込みをしていなかったため、別日の同一得点のデータが抽出されてしまっていますね。
試験日フィールドにも抽出条件を設定しておきます。
実行してみると、今度は期待どおり生徒ごとの指定日の最高点のレコードが抽出されました。
正直、このケースについて、私は理解に苦労してしまいました。
条件式がある程度複雑になるようであれば、最初に紹介した中間クエリを使う方法の方が、わかりやすいんじゃないかな〜と思います。
補足：別のテーブルからもデータ取得できる
先ほどの例は、テスト結果テーブルだけで全て完結する例でした。
DMax関数・DMin関数は、他のテーブルからデータを取得することもできるので、サンプルを紹介します。
新たに生徒一覧テーブルを用意しました。
生徒一覧テーブルのフィールドに加え、テスト結果テーブルから取得したその生徒の最高得点を取得してみます。
最高得点: DMax("得点","テスト結果","ID = '" & [ID] & "'")実行した結果は下図のとおりです。
あまりいいユースケースが思い付かず、こんな感じの例になってしまいました。
個人的には、このパターンではDMax関数を使わず、両テーブルを結合し、グループ化して最大値を取得するほうが好きです。
コメント