Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Access 2016)
workday.intlのような抽出方法
投稿日時: 19/03/23 14:48:10
投稿者: kasama

ステータスというクリエに営業所、営業開始日、品目、調達リードタイム、販売日の5フィールドがあります。
・営業所は5店舗あり、営業開始日は5店舗とも異なります。
・品目は10品目あり、5店舗とも同じ品目を販売してます。
・10品目は、それぞれ調達リードタイムが異なっています。
 
この時のエクセルのworkday.intlのような形で販売日を出したいです。
エクセルだと=workday.intl(営業開始日,調達リードタイム,1,休日)という形でですが
このような事がAccess で可能でしょうか?

回答
投稿日時: 19/03/24 14:40:27
投稿者: hatena
投稿者のウェブサイトに移動

ACCESSには、workday.intlに相当する関数、機能はないので、ユーザー定義関数を自作することになるでしょう。
 
以前に似たようなもの作ったことがあるので紹介しておきます。
休業日を登録するテーブルを作成します。
 
T_祝日
 日付 日付/時刻型 主キー
 祝日名 テキスト型
 
日付    祝日名
-------------------
2019/01/01 元旦
2019/01/14 成人の日
2019/02/11 建国記念日
・・・・・
 
会社独自の休業日等も入力しておいてください。
 

Public Function MyWorkday(開始日 As Date, 日数 As Long) As Date
  Dim 営業日 As Long
  MyWorkday = 開始日
  Do
    MyWorkday = MyWorkday + 1
    Select Case Weekday(MyWorkday)
    Case vbMonday To vbFriday
      If IsNull(DLookup("祝日名", "T_祝日", "日付=#" & 許可日 & "#")) Then
        営業日 = 営業日 + 1
      End If
    End Select
  Loop Until 営業日 >= 日数
End Function

 
土日が休業日という前提です。
違う場合は、
 
    Case vbMonday To vbFriday

の部分を変更してください。

回答
投稿日時: 19/03/24 21:00:33
投稿者: mayu.

引用:
エクセルだと=workday.intl(営業開始日,調達リードタイム,1,休日)という形でですが
このような事がAccess で可能でしょうか?

カレンダーテーブルの設計を工夫することで
一般機能でも実現が可能になりますから、そのサンプルも載せておきます。
 
■ T_休日
 
・日付    ( 日付/時刻型 ) ※ 主キー
・休日フラグ ( 数値型 )
・順番    ( 数値型 )
 
< データ例 >
 
  日付   休日フラグ  順番
----------------------------------
 2019/04/01   0      1
 2019/04/02   0      2
 2019/04/03   0      3
 2019/04/04   0      4
 2019/04/05   0      5
 2019/04/06   1      5
 2019/04/07   1      5
 2019/04/08   0      6
   ・    ・      ・
   ・    ・      ・
   ・    ・      ・
 2019/04/25   0      19
 2019/04/26   0      20
 2019/04/27   1      20
 2019/04/28   1      20
 2019/04/29   1      20
 2019/04/30   1      20
 2019/05/01   1      20
 2019/05/02   1      20
 2019/05/03   1      20
 2019/05/04   1      20
 2019/05/05   1      20
 2019/05/06   1      20
 2019/05/07   0      21
 2019/05/08   0      22
   ・    ・      ・
   ・    ・      ・
   ・    ・      ・
 2019/05/30   0      38
 2019/05/31   0      39
 
# 例ではとりあえず 土日祝日を休日 としています。
 
何年何月から日付を登録するか、最終の日付はいつにするか
というのは、好きに決めていただいて結構ですけど
順番フィールドの入力ルールは
 
 ・ 連番を振っていきますが、休日の場合は番号を進めない
 ・ 欠番は認めない
 
とします。
 
なお、カレンダーは EXCELで作成して、A1,B1,C1セルにフィールド名を記述し、
A列 と B列 に値を入力してから
C2 には 1 を直接入力、
C3 は =If(B3= 0, C2 + 1, C2)
C3 以降は C2 のオートフィル機能を使って値を設定した上で
シートを Access にインポートするのが簡単でしょう。
 
■ T_ステータス
 
・営業開始日      ( 日付/時刻型 )
・品目        ( テキスト型 )
・調達リードタイム  ( 数値型 )
 
< データ例 >
 
 営業開始日   品目  調達リードタイム
--------------------------------------------
 2019/04/01  パイン     8
 2019/04/01  りんご     0
 2019/04/18  バナナ     2
 2019/04/26  みかん     1
 2019/04/28  みかん     1
 2019/04/28  りんご     0
 
■ SQL
-------------------------------------------------------------------
SELECT x.営業開始日
     , x.品目
     , x.調達リードタイム
     , IIf( z.日付 < x.営業開始日, x.営業開始日, z.日付 ) As 販売日
FROM T_ステータス x
   , T_休日       y
   , T_休日       z
WHERE x.営業開始日 = y.日付
  AND z.順番 = x.調達リードタイム + y.順番
  AND z.休日フラグ = 0
ORDER BY 1 ;

■ 結果
 
 営業開始日   品目  調達リードタイム   販売日
--------------------------------------------------------
 2019/04/01  パイン     8      2019/04/11
 2019/04/01  りんご     0      2019/04/01
 2019/04/18  バナナ     2      2019/04/22
 2019/04/26  みかん     1      2019/05/07
 2019/04/28  みかん     1      2019/05/07
 2019/04/28  りんご     0      2019/04/28
 
 
# T_休日 テーブルの休日フラグを編集したり、日付データの追加削除などで
# 順番フィールドの連番を振りなおす場合は、以下のような更新クエリを順番に発行します。
 
UPDATE T_休日 SET 順番 = 1
WHERE 日付 = ( SELECT TOP 1 日付 FROM T_休日 ORDER BY 日付 ) ;

UPDATE 
(
    SELECT x.日付
         , x.休日フラグ
         , x.順番
         , y.順番 As ref
    FROM T_休日 x
       , T_休日 y
    WHERE x.日付 = y.日付 + 1
    ORDER BY x.日付
) q
SET 順番 = ref + IIf( 休日フラグ = 0, 1, 0 ) ;

トピックに返信