ここから本文です

【ACCESS】他のレコードと比較して日付の期間が空いてるレコードを抽出したいです。

nag********さん

2019/8/100:21:59

【ACCESS】他のレコードと比較して日付の期間が空いてるレコードを抽出したいです。

NO | 会員番号 | 適用日 | 終了日
1 050001 20190101 20190131
2 050001 20190201 20190328
3 050001 20190330 20190331
4 050001 20190406 20190430


上記のようなリストがあるのですが、適用日と終了日で間が空いている期間があるレコードを抽出したいです。

例えば、、、

NO1の終了日とNO2の適用日の間には1日しか変わりがなく、これは問題ないのですが
NO2の終了日とNO3の適用日の間には2日ずれがあり、1日空白が空いていることになります。

この空白が空いている期間を会員番号ごとに抽出したいです。


例えば、、、
NO3の終了日とNO4の適用日の間には空白の期間が
20190401から20190405あるので
これを


NO | 会員番号 | 空白適用日 | 空白終了日
1 050001 20190401 20190405

といったぐあいに抽出したいです
また1人の会員に対して複数空白の期間があった場合、それらもすべて出力したいです。


ACCESSでいけるのでしょうか…なにかいい方法などはないでしょうか・・・
御助力お願いいたします。

補足日付部分はたしかに数値でしたが、後から日付型に直しました。

閲覧数:
47
回答数:
3
お礼:
50枚

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

hot********さん

2019/8/112:26:02

VBAの場合です。
元テーブルが"テーブル1"の場合です。
同じフィールドの"結果"テーブルをあらかじめ用意してください。
適用日と終了日のフィールドが日付/時刻型の場合です。

Sub sample()
Dim rs As Recordset
Dim c As Integer
Dim n As Variant
Dim i As Integer
Dim d As Variant
Dim j As Integer
CurrentDb.Execute "DELETE * FROM 結果" '結果クリア
Set rs = CurrentDb.OpenRecordset("SELECT 会員番号 FROM テーブル1 GROUP BY 会員番号 ORDER BY 会員番号") '会員番号の取り込み
c = 1 'カウントの初期値=1
n = rs.GetRows(rs.RecordCount) 'レコードを配列に取り込む
For i = 0 To UBound(n, 2) '会員番号を順に
Set rs = CurrentDb.OpenRecordset("SELECT 適用日,終了日 FROM テーブル1 WHERE 会員番号=" & n(0, i) & " ORDER BY 適用日") '会員番号毎の適用日と終了日取り込み
d = rs.GetRows(rs.RecordCount) 'レコードを配列に取り込む
For j = 0 To UBound(d, 2) - 1 '注目レコードを最初のレコードから最後のレコード-1まで
If DateDiff("d", d(1, j), d(0, j + 1)) > 1 Then '注目レコードの終了日から次のレコードの適用日の間が1日以上なら(ブランクの日があるなら)
CurrentDb.Execute "INSERT INTO 結果 (NO,会員番号,適用日,終了日)VALUES(" & c & "," & n(0, i) & ",#" & DateAdd("d", 1, d(1, j)) & "#,#" & DateAdd("d", -1, d(0, j + 1)) & "#)" 'C(カウント)、会員番号、適用日:終了日の翌日、終了日:次の適用日の前日 結果に追加
c = c + 1 'カウント+1
End If
Next
Next
End Sub

  • 質問者

    nag********さん

    2019/8/514:03:41

    ご教示ありがとうございます。

    >For i = 0 To UBound(n, 2) '会員番号を順に
    ここの箇所で「オーバーフローしました」
    と出てしまいました...
    番号が文字列だからでしょうか?

  • その他の返信を表示

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

質問した人からのコメント

2019/8/8 00:03:37

ありがとうございます!
エクセルでデータ型を指定しなおしてアクセスに取り込みなおしたらうまくいきました。
本当に感謝です。
愛しております。

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

tom********さん

2019/8/113:47:42

もともとの日付フィールドが「日付/時刻型」ならば
選択クエリーを作り
間隔日数:Datediff("d",[開始日],[終了日])と設定すればお求めのものとなります。

日付フィールドが8桁のテキストの場合ならDateSerial()を使用して日付型に変換しさらにDatediff()で間隔を求める。
間隔日数: DateDiff("d",DateSerial(Mid([開始日],1,4),Mid([開始日],5,2),Mid([開始日],7,2)),DateSerial(Mid([終了日],1,4),Mid([終了日],5,2),Mid([終了日],7,2)))

いずれの場合もDateDiff()関数で日付同士の間隔を求められます。

プロフィール画像

カテゴリマスター

nor********さん

2019/8/102:03:45

「カレンダー」テーブルみたいのがあればできそうですが。

> NO | 会員番号 | 適用日 | 終了日
> 1 050001 20190101 20190131

適用日、 終了日が「日付/時刻型」でなさそうに見えて、それだとめんどくさそう。

この質問につけられたタグ

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる