MS-ACCESS でテーブルAの日付を元にテーブルBの中の一番近い日付のデータを取得する方法を教えてください。

Question

いつもお世話になっております。

かなりハマってしまっております。是非アドバイスをお願いします。
下記のようなテーブルA、テーブルBがあります。
各担当者毎に状況を確認するため担当者を選択することで基本情報と出勤状況、またその出勤日に一番近い日付の売上データを紐づけたいと思っております。

<テーブルA>出勤情報
担当CD|出勤日|コメント
01|2021/7/1|7月最初の日
02|2021/7/1|今日から7月
01|2021/7/3|ルートAを訪問
03|2021/7/4|ワクチン接種
01|2021/7/6|ルートBを訪問

<テーブルB>売上情報
担当CD|売上日付|商品CD|金額
01|2021/6/30|00020|12,000
01|2021/7/1|00010|10,000
01|2021/7/2|00030|15,000
03|2021/7/3|00100|18,000
01|2021/7/5|00200|9,000
01|2021/7/5|00300|7,000

例えば担当CD「01」の情報を確認した場合、出勤情報と売上情報は下記のように紐づけたいのです。
紐づけはヒットした日付のデータのみです。
(状況一覧は日付の降順ソート)

担当01
出勤日｜コメント｜売上日付｜商品CD｜金額
2021/7/6|ルートBを訪問|2021/7/5|00200|9,000
2021/7/6|ルートBを訪問|2021/7/5|00300|7,000
2021/7/3|ルートAを訪問|2021/7/2|00030|15,000
2021/7/1|7月最初の日|2021/7/1|00010|10,000

このような処理はMS ACCESSのクエリで行えるでしょうか？
アドバイスの程よろしくお願いいたします。

Comments

[sazi]

> 出勤日に一番近い日付の売上データを紐づけたい
出勤日以降での直近の日付ならまだしも、出勤日より過去の売上日が紐付くのはおかしくないですか？

[Yamachan777]

sazi様
通常そのように思いますが、どうやら出勤日の前の動向を把握したいらしいのです。
出勤日と同じか、もしくは出勤日以前の直近の動向把握が目的のようです。

よろしくお願いします。

[sazi]

出勤日無しの売上があるという事ですか？
売上入力時に出勤日の入力が無いかどうかチェックすれば済む話だと思いますけどね。
それは置いとくとして、日付間隔で紐づけする場合に同じ間隔だったらどうするんでしょう。

[Yamachan777]

sazi様

実際は出勤日無しの売上という認識ではなく、出勤日以前の動向把握をしたいとのことです。

ですので日付間隔ではなく、出勤日以前の売上データがあればそれに紐づける感じです。
EXCELを使って手作業で出来るのかもしれませんが、自動化したいとのことで悩んでおります。。。

ご回答ありがとうございます。
早速確認してみます。

[sazi]

「出勤日以前」とは出勤日を含みますか？
内容からは「出勤日より過去日」として出勤日を含まないように受け取れます。
また、出勤日以降の売上日付は関係ないという事ですね。

[Yamachan777]

sazi様

「出勤日以前」は出勤日を含みます。
説明がわかりづらく申し訳ありませんでした。
出勤日以降の売上は無視して構いません。

お手数をおかけしますが、よろしくお願いします。

[sazi]

>「出勤日以前」は出勤日を含みます。
異なる出勤日に同一の売上日が紐付く事はあるという事ですか。

[Yamachan777]

そうです。あり得ます。

[sazi]

#コメントが前後しました
出勤日の間に含まれる売上日付という要件では無いという事ですね。

[Yamachan777]

その通りです。各出勤日以前の売上日付をもった売上データとの紐づけで、売上日付は該当した1日と対応する感じです。
仮に出勤日が7/6,7/3とあり、売上日付が7/3のものしかない場合は、7/6,7/3の出勤日それぞれに7/3の売上が紐づく感じです。

Answer 1

こんな感じでしょうか。
※デバッグはしていませんので、Nullの考慮などが必要かもしれません。

select ta.*, tb.*
from ((
        select *
             , IIf(DateDiff("d", 出勤日, 以前直近売上日) >= DateDiff("d", 出勤日, 以降直近売上日), 以降直近売上日, 以前直近売上日) 売上日付 
        from (
            select ta.担当CD, ta.出勤日
                 , DMax("売上日付", "テーブルB", "担当CD='" & 担当CD & "' and 売上日付 <= #" & 出勤日 & "#") as 以前直近売上日
                 , DMin("売上日付", "テーブルB", "担当CD='" & 担当CD & "' and 売上日付 >= #" & 出勤日 & "#") as 以降直近売上日
            from テーブルA as ta
         ) as t
    ) as 基準
    inner join テーブルA as ta
    on    基準.担当CD=ta.担当CD
      and 基準.出勤日=ta.出勤日
    ) left join テーブルB as tb
    on    基準.担当CD=tb.担当CD
      and 基準.売上日付=tb.売上日付
where 基準.担当CD='01'
order by ta.担当CD, ta.出勤日 desc

追記

出勤日以降の売上日付は関係ないという事だと以下の様な感じ

select ta.*, tb.*
from ((
      select ta.担当CD, ta.出勤日
           , DMax("売上日付", "テーブルB", "担当CD='" & 担当CD & "' and 売上日付 <= #" & 出勤日 & "#") as 売上日付
      from テーブルA as ta
    ) as 基準
    inner join テーブルA as ta
    on    基準.担当CD=ta.担当CD
      and 基準.出勤日=ta.出勤日
    ) left join テーブルB as tb
    on    基準.担当CD=tb.担当CD
      and 基準.売上日付=tb.売上日付
where 基準.担当CD='01'
order by ta.担当CD, ta.出勤日 desc

サブクエリーを使用するとこんな感じ。

select ta.*, tb.*
from ((
      select ta.担当CD, ta.出勤日
           , (select Max(売上日付) from テーブルB where 担当CD = ta.担当CD and 売上日付 <= ta.出勤日) as 売上日付
      from テーブルA as ta
    ) as 基準
    inner join テーブルA as ta
    on    基準.担当CD=ta.担当CD
      and 基準.出勤日=ta.出勤日
    ) left join テーブルB as tb
    on    基準.担当CD=tb.担当CD
      and 基準.売上日付=tb.売上日付
where 基準.担当CD='01'
order by ta.担当CD, ta.出勤日 desc

#追記2
出勤日と前の出勤日の間の売上日付の場合は以下

select ta.*, tb.*
from ((
      select ta.担当CD, ta.出勤日
           , DMax("出勤日", "テーブルA", "担当CD='" & 担当CD & "' and 出勤日 <= #" & 出勤日 & "#") as 前出勤日
      from テーブルA as ta
    ) as 基準
    inner join テーブルA as ta
    on    基準.担当CD=ta.担当CD
      and 基準.出勤日=ta.出勤日
    ) left join テーブルB as tb
    on    基準.担当CD=tb.担当CD
      and 基準.出勤日 >= tb.売上日付 and 基準.前出勤日 < tb.売上日付
where 基準.担当CD='01'
order by ta.担当CD, ta.出勤日 desc

Comments

[Yamachan777]

sazi様

教えていただいたSQLをAccessに登録して実行しましたら
「入力テーブルまたはクエリ'テーブルA'が見つかりませんでした。そのテーブルやクエリが存在していること、または名前が正しいことを確認してください。」
のエラーメッセージが表示されました。

SQL分内の「テーブルA ta」を「ta」に、「テーブルB tb」を「tb」に変更してみました。
この変更が正しいのかわかりませんが。。。
それで実行すると、今度は
「クエリ式’IIf(DateDiff("d", 出勤日, 以前直近売上日) >= DateDiff("d", 出勤日, 以降直近売上日), 以降直近売上日, 以前直近売上日) 売上日付 ’の構文エラー：演算子がありません。」のエラーが発生しました。
取り急ぎご報告まで。
引き続きデバッグしてみます。

[sazi]

「入力テーブルまたはクエリ'テーブルA'が見つかりませんでした。」については実際のテーブルに置き換えて下さい。
taなどのエイリアスについては Asが必要だったかもしれませんので、回答を修正しました。

[Yamachan777]

「式で型が一致しません」のエラーが出ますが、担当CDはテキスト形式、その他は日付形式ですので、問題ないように思うんですが？
いちいち基本的なことで申し訳ありません。

[sazi]

どの回答分ですか？
取り敢えず、追記分のサブクエリー版を追記しておきます。

[Yamachan777]

ありがとうございました。
思った通りのデータが取得できました。
本当に助かりました。
引き続きよろしくお願いいたします。