アクセスで完全一致でない日付の場合の抽出

質問

質問をするにあたってよい例示が出せていないかもしれませんがお助けください。ID番号でたずさわった人を年と月でそれぞれデータを登録しています。

下記のように年度と月は降順でそれぞれ並びかえしています

| ID | 名前　| 年度 | 月 |
| aa | A子 　|2006　| 12 |
| aa | B子 　|2006　| 12 |
| aa | C子 　|2006　| 12 |
| bb | D子 　|2006　| 12 |
| bb | E子 　|2006　| 12 |
| bb | F子 　|2006　| 12 |
| aa | A子 　|2006　| 07 |
| aa | B子 　|2006　| 07 |
| aa | A子 　|2005　| 12 |
| aa | C子 　|2005　| 12 |

このデータから今日の日付(2007年1月)と比較して　ID aa　の一番近い年と月のデータを抽出したいのですがどのようなクエリを組めばよいのか教えてほしいのです。
求める回答としては上から3つのデータを抽出するようにしたいのです。ただしこの場合は3件ですがデータの登録によって月のデータは増減するものと考えてください。
よろしくお願いいたします。

回答No.2

venzou · ベストアンサー

SELECT T2.*
FROM テーブル名 AS T2 INNER JOIN
(
SELECT T1.ID, Max(100*T1.年度 + T1.月) AS 年月
FROM テーブル名 AS T1
WHERE 100*T1.年度 + T1.月 <= 100*Year(Date())+Day(Date())
GROUP BY T1.ID
) AS Q1
ON T2.ID = Q1.ID
WHERE 100*T2.年度 + T2.月 = Q1.年月
ORDER BY T2.ID;

こんな感じで求まると思います。
全てのIDのデータが出ますので、必要ならWHEREで条件を追加してください。

-------------------------------------
質問は「例」ということなので、本番用に、簡単に説明しておきます。

上記は1つのクエリにまとめていますが、2つのクエリに分けたほうが理解しやすいと思います。

1:IDでグループ化し年月の最大値を求めるクエリを作る

ポイント
・GROUP BY T1.ID
・WHERE 100*T1.年度 + T1.月 <= 100*Year(Date())+Day(Date())
・Max(100*T1.年度 + T1.月) AS 年月

データに未来のデータが無ければWHERE句は不要です。

年月のまとめ方ですが、私は数値化しています。文字列のままで連結しても可ですが、データ入力で"07"を間違えて"7"と入れたりすると不具合が出ますので注意してください。（年と月は予め数値で入力しておく方がベターだと思います。）

上記を1つのクエリとして保存してください。Q1とします。

2:先ほど作ったクエリQ1と、テーブルを結合して、新しいクエリを作る

ポイント
・ON T2.ID = Q1.ID
・WHERE 100*T2.年度 + T2.月 = Q1.年月

これはさほど難しくないと思います。
１と同様に年月を数値でまとめています。

お礼

体調を崩してしまいパソコンから離れておりましたのでお礼ができませんでした。遅くなりましたがご回答いただき有難うございました。

回答No.3

CHRONOS_0

>bbの場合には|2006 | 11 |のデータを引っ張れるようにしたいのです。
当然そうでしょうね
aaだけでいいのかなと疑問に思っていました

その場合の抽出条件は

In (select max([年度] & [月]) from abchan as A where [年度] & [月]<=format(date(),"yyyymm") and A.ID=abchan.ID)

お礼

お礼が遅くなり申し訳ございませんでした。ご回答有難うございました。

回答No.1

CHRONOS_0

年と月とは一続きのものです
別々に比較したのでは正しい結果は得られません

本来ならフィールドを分けないほうがやりやすいですよ

分けてあるなら仕方ないですから、結合したフィールドを作り
>今日の日付(2007年1月)と比較して　ID aa　の一番近い年と月
回りくどい言い方ですが未来の年月も入力されているのでしょうか

過去の日付だけなら単純に最大値ですね
未来の日付もあるのなら未来を除いて最大を求めるだけです

[年度] & [月]という式フィールドを作り
そのフィールドの抽出条件欄に

In (select max([年度] & [月]) from テーブル名 where [年度] & [月]<=format(date(),"yyyymm"))

お礼

CHRONOS_0様　回答ありがとうございました。結合させて最大値をとるやり方ですね。過去分しかデータはありませんのでこの式でやってみようと思います。今はアクセスなのですがoracleとSQLserverにもデータを移行する予定ですので結合方法はそれぞれ違うやり方でしないといけないんですよね。
何はともあれお答えいただきまして有難うございました。

補足

CHRONOS_0様 新しいフィールドを試してみました。確かに200612だけを抽出しました。それでなんですがやはり私の質問の例示も悪かったかもしれません。下記でもう一度考えていただけないでしょうか？

| ID | 名前　| 年度 | 月 |
| aa | A子 　|2006　| 12 |
| aa | B子 　|2006　| 12 |
| aa | C子 　|2006　| 12 |
| bb | D子 　|2006　| 11 |
| bb | E子 　|2006　| 11 |
| bb | F子 　|2006　| 11 |
| aa | A子 　|2006　| 07 |
| aa | B子 　|2006　| 07 |
| bb | D子 　|2005　| 11 |
| aa | C子 　|2005　| 12 |

上の抽出条件ですと年と月の結合最大値だけが抽出されてしまいます。ここではIDを問い合わせてbbの場合には|2006 | 11 |のデータを引っ張れるようにしたいのです。この場合はどのようにすればよいでしょうか？