Microsoft Access 掲示板

一時的な日付範囲指定を含む日付データからの抽出

8 コメント

views

4 フォロー

指定日付>=開始日 AND ISNULL(終了日)=FALSE で抽出後

これ、結果は一意じゃないですよね。2レコード以上HITしたらどうしてるんですか？

このようなデータで日付を指定した際に
指定日付　2021/08/01　～　2021/08/04　基準開始日　2021/08/01　　単価150　　
指定日付　2021/08/05　～　2021/08/10　基準開始日　2021/08/05　　単価120
指定日付　2021/08/11　～　2021/08/19　基準開始日　2021/08/01　　単価150
指定日付　2021/08/20　～　　　　　　基準開始日　2021/08/20　　単価140
と基準開始日と単価が返ってくるようにしたいのです

このデータ形式でテーブルを作らないのは何故なんでしょう？

2レコード以上は範囲入力側の制御の時点で入れられない形にしています
>(8/5～8/10 ＆ 8/6～8/11のような複数で日付が被る形はありません)

あくまで終了日付が入るのは期間限定の臨時の時だけで基本的には開始日で単価を制御するようになっており、
単価情報を入力する側が期間限定明け後のデータ入力（ここでは8/11～8/19）を意識しなくてもいい形にしたいというものです

例えば

開始日	終了日	単価
2021/08/01	Null	150
2021/08/05	2021/08/10	120
2021/08/11	2021/08/19	130
2021/08/20	Null	140

のようなデータの時、指定日=2021/08/15の場合

指定日付>=開始日 AND ISNULL(終了日)=FALSE で抽出

すると

開始日	終了日	単価
2021/08/05	2021/08/10	120
2021/08/11	2021/08/19	130

と、2レコードHITします

2レコード以上は範囲入力側の制御の時点で入れられない形にしています

これは、つまり、このようなレコードは存在しないということでしょうか？だとすると、終了日に値の存在が許されるレコードは1つだけという歪なテーブル構造なんでしょうか？

質問の解自体はhatenaさんの回答の通りでしょう。ただ、手順や処理時間で見た簡略化は見込めないと思います。この観点から言えば

これ以上簡単にできる方法は

ないです

しかしながら、データ構造の観点から言えば、「一時的に変わる時」のデータは単純に追加するだけで済むというメリットができ、データ管理面での単純化なら見込めます

さらに言えば、既に指摘している通り、なぜ最終的なデータ構造でテーブルを作らないのか？という話になります。データ構造が単純で、データの取得もシンプルです
単純＝「簡単」とは限らないですが、テーブル構造からはわからない独自ルールを運用するよりはよほど「簡単」でしょう。

単価情報を入力する側が期間限定明け後のデータ入力（ここでは8/11～8/19）を意識しなくてもいい形にしたいというものです

本当の主題はこれですよね？
ならば、職人芸のようなテーブル構造・データ取得を考えるよりも単純に「期間限定明け後のデータ入力（ここでは8/11～8/19）を」自動化したいと考えるべきでしょう

hirotonさんの提示したテーブルのような形にしない限りは、簡単にはならないと思います。

どのようなUIなのか不明なので、
フォーム上に「日付」と「単価」というテキストボックスがあると仮定すると、
下記のようなコードで期待する結果にはなります。

Private Sub 日付_AfterUpdate()
    Dim res
    
    res = DLookup("単価", "単価マスタ", "#" & Me.日付 & "# Between 開始日 AND 終了日")
    
    If IsNull(res) Then
        res = DMax("開始日", "単価マスタ", "#" & Me.日付 & "# >=開始日 AND 終了日 Is Null")
        If Not IsNull(res) Then
            Me.単価 = DLookup("単価", "単価マスタ", "#" & res & "#=開始日")
        End If
    Else
        Me.単価 = res
    End If    
End Sub

hirotonさんの提示したテーブルなら、下記の1行のコードで済みます。

Me.単価 = DLookup("単価", "単価マスタ", "#" & Me.日付 & "# Between 開始日 AND 終了日")

例
開始日　　　　　終了日　　　　　単価
2021/08/01　　　Null　　　　　　150
2021/08/05　　　2021/08/10　　　120
2021/08/20　　　Null　　　　　　140

このようなデータで日付を指定した際に
指定日付　2021/08/01　～　2021/08/04　基準開始日　2021/08/01　　単価150　　
指定日付　2021/08/05　～　2021/08/10　基準開始日　2021/08/05　　単価120
指定日付　2021/08/11　～　2021/08/19　基準開始日　2021/08/01　　単価150
指定日付　2021/08/20　～　　　　　　基準開始日　2021/08/20　　単価140
と基準開始日と単価が返ってくるようにしたいのです

　　
私個人は、hirotonさんやhatenaさんと同じく
ソリューションとしては圧倒的に
{ テーブルデータの保有形式を変更 }　＞＞＞　{ トリッキーな SQL または VBA }
という見解です。

ただ Access の一般機能で「実現可能」なご要望であることも事実ですから
実用性とパフォーマンスは度外視で、SQL( クエリ )のサンプル を載せておきます。
( ※ 今回の投稿はテーブル定義とデータ例のみ )

なお、( 何らかの )日付範囲の抽出条件を満たした後のデータが
「例」の３行であるとするなら

「 指定日付 」に該当するようなパラメータなりリテラル値は
開始日フィールドに指定された可能性が高いと推測し、
私の回答( SQL )では、現状のテーブルデータに影響を与えない抽出条件として

    開始日 >= dateserial( year( date() ), month( date() ), 1 )

を指定しています。
　　

■テーブル：単価マスタ

CREATE TABLE 単価マスタ
(
      rid    counter  not null primary key
    , 開始日 datetime not null
    , 終了日 datetime
    , 単価   money    not null
);

rid	開始日	終了日	単価
1	2021/08/01		150
2	2021/08/05	2021/08/10	120
3	2021/08/13		100
4	2021/08/20	2021/08/22	140
5	2021/08/24		15
6	2021/08/28		30

　　
　　
■テーブル： T_num　　

CREATE TABLE T_num ( num int not null primary key );

( T_num テーブルの num には 0 ～ 9 までの数値を入力 )

num
0
1
2
3
4
5
6
7
8
9

( 続き )

＃最初は SQLの一筆書きを投稿しようとしたところ
＃ 160行の記述が長すぎて投稿不可であったことと
＃サブクエリのネストが深くて、記述が複雑怪奇になったため、
＃構造別にクエリを複数( 計7つ )作るという回答に切り替えました。

まぁ...個々の記述が短いと最終結果が出るまでの工程も分かりやすいでしょう。
( クエリを作る順番も、掲載した順と同じになります )

SQL文
クエリ名
SQLの結果セット

SELECT 開始日
     , cdate(
           nz( 
               x.終了日
             , nz( 
                   ( 
                     SELECT min( 開始日 ) - 1 
                       FROM 単価マスタ y 
                      WHERE y.開始日 >= dateserial( year( date() )
                                                  , month( date() )
                                                  , 1
                                        )
                        AND y.開始日 > x.開始日
                   )
                 , dateserial( 2099, 12, 31 )
               )
          ) 
      ) As 終了日
    , 単価
FROM 単価マスタ x
WHERE 開始日 >= dateserial( year( date() ), month( date() ), 1 ) ;

q_base

開始日	終了日	単価
2021/08/01	2021/08/04	150
2021/08/05	2021/08/10	120
2021/08/20	2021/08/22	140
2021/08/13	2021/08/19	100
2021/08/24	2021/08/27	15
2021/08/28	2099/12/31	30

SELECT dateadd( 'd', x.num + y.num * 10, z.d_min ) As ymd
FROM T_num x
   , T_num y
   , ( 
         SELECT min( 開始日 ) As d_min
              , datediff( 'd', min( 開始日 ), max( 開始日 ) ) As d_count
         FROM 単価マスタ
         WHERE 開始日 >= dateserial( year( date() ), month( date() ), 1 )
     ) z
WHERE x.num + y.num * 10 < z.d_count ;

q_ymd

ymd
2021/8/1
2021/8/2
2021/8/3
( 省略)
2021/8/25
2021/8/26
2021/8/27

SELECT x.ymd
FROM      q_ymd x
LEFT JOIN q_base y
       ON ( ( x.ymd between y.開始日 and y.終了日 ) )
WHERE y.開始日 Is Null ;

q_ymdlist

ymd
2021/8/11
2021/8/12
2021/8/23

( 続き )
　

SELECT min( ymd ) As d_from
     , max( ymd ) As d_to
FROM
(
    SELECT x.ymd
         , count(1) As n
    FROM q_ymdlist x
       , q_ymdlist y
    WHERE x.ymd >= y.ymd
    GROUP BY x.ymd
) q
GROUP BY ymd - n ;

q_fromto

d_from	d_to
2021/08/11	2021/08/12
2021/08/23	2021/08/23

SELECT x.d_from
     , x.d_to
     , max( iif( y.終了日 Is Null, y.開始日 ) ) As high
     , max( y.開始日 ) As low
FROM q_fromto x
LEFT JOIN 単価マスタ y
       ON ( 
              (
                  x.d_from > y.開始日
                      AND
                  y.開始日 >= dateserial( year( date() ), month( date() ), 1 )
              )
          )
GROUP BY x.d_from
       , x.d_to ;

q_highlow

d_from	d_to	high	low
2021/08/11	2021/08/12	2021/08/01	2021/08/05
2021/08/23	2021/08/23	2021/08/13	2021/08/20

SELECT x.d_from
     , x.d_to
     , y.開始日 As 基準開始日
     , 1 As 補填行
     , y.単価
FROM q_highlow x
   , 単価マスタ y
WHERE cdate( nz( x.high, x.low ) ) = y.開始日 ;

q_union

d_from	d_to	基準開始日	補填行	単価
2021/08/11	2021/08/12	2021/08/01	1	150
2021/08/23	2021/08/23	2021/08/13	1	100

SELECT 開始日 As 指定開始日
     , 終了日 As 指定終了日
     , 開始日 As 基準開始日
     , 0 As 補填行
     , 単価
FROM q_base
UNION ALL
SELECT d_from
     , d_to
     , 基準開始日
     , 補填行
     , 単価
FROM q_union
ORDER BY 1 ;

q_result

指定開始日	指定終了日	基準開始日	補填行	単価
2021/08/01	2021/08/04	2021/08/01	0	150
2021/08/05	2021/08/10	2021/08/05	0	120
2021/08/11	2021/08/12	2021/08/01	1	150
2021/08/13	2021/08/19	2021/08/13	0	100
2021/08/20	2021/08/22	2021/08/20	0	140
2021/08/23	2021/08/23	2021/08/13	1	100
2021/08/24	2021/08/27	2021/08/24	0	15
2021/08/28	2099/12/31	2021/08/28	0	30

ありがとうございます
クエリで日付から単価や開始日を呼び出したりする形で使いたかったので
hatenaさんのコードをもとに標準モジュール（日付を入数化）にする形で対応できました

タグリスト

VBA (368) Office2016 (291) フォーム (228) Office2021 or 2019 (221) クエリ SQL (211) テーブル (134) レポート (123) Office2013 (56) Office2010 (47) Microsoft365 (42)

Markdown テーブル生成

Accessのテーブルやクエリのデータを貼り付ける場合は下記で、Markdown書式のテーブルに変換して貼り付けてください。

Markdown Tables generator

ファイル送信フォーム

掲示板で必要なサンプルファイルは下記のフォームから送信してください。

Microsoft Access 掲示板用ファイル送信

Microsoft Access 掲示板

一時的な日付範囲指定を含む日付データからの抽出

最新トピック

タグリスト

Markdown テーブル生成

ファイル送信フォーム