翌月最終営業日の求め方（ＡＣＣＥＳＳ 2002）

ACCESS初心者です。
ある日をもとに、翌月の最終営業日を求めるクエリの作成方法をご教示ください。
通常月は土日祝日が休み、年末(12月）は29日から休みとなります。
宜しくお願い致します。

質問者からの補足コメント

• ご回答ありがとうございます。
  
  今、注文をデータ入力し、請求書を作成する、というものをaccessで構築しています。
  
  質問で記した「ある日」とは「請求書発行日」（大体はお客様からの発注日と同じ）のことで、日付はデータを入力した日となります。
  
  請求書に支払期日を示すのですが、それは請求日（請求書発行日）の翌月の最終営業日としており、それを請求書（というレポート）作成時に報じするようにしたいと思っています。
  
  ＥＸＣＥＬでしたら、ＷＯＲＫＤＡＹとＷＥＥＫＤＡＹ関数を組み合わせれば設定できると判るのですが、accessは初心者なのでどのように設計すれば良いかわからない状況です。
  
  引続きのご説明の程宜しくお願い致します。

  No.1の回答に寄せられた補足コメントです。 補足日時：2017/02/27 13:56

• ご回答ありがとうございます。
  
  月末最終営業日は、土日祝日でなければその日、土日祝日、休業日にあたる場合は、その直前の平日、ということを、請求日から自動で計算させたいと思っています。
  
  今EXCELで作成している請求書では、
  WORKDAY(EOMONTH(請求書日付,1)+1,-1, 休業日
  という関数を使い、この関数の「休業日」についてはリストをもたせそこを参照するようにしています。
  
  仰る「テーブル」というのは、私が言うところのEXCELの「休業日」リストと同じという認識でよろしいでしょうか？
  もし、同じであるとするなら、確かに祝日がその年によって決まることもありますし、休業日が変わることもありうるので、こういうテーブルをもつ方がいいのかと思います。

  No.3の回答に寄せられた補足コメントです。 補足日時：2017/02/28 11:29

• 私の質問の仕方がよくなかったようですみません。
  
  今までは、販売データを蓄積することなく請求書を単体で作成していたのですが、今般販売データを蓄積し、請求書を作成するという一連のことをaccessで行うということで、設計しているというのがそもそも背景にあります。
  
  仰る通り、年月が与えられれば、最終営業日は１つの日付しかありません。例えば今年の２月に請求すれば、その日がいつであろうと支払期日である翌月の最終営業日は３月３１日になりますし、今年の３月に請求すれば、４月２８日となります。この３月３１日や４月２８日を求めるにはどのように設計すればよいのか、というのが私の質問です。
  
  説明がうまくなくて済みません。

  No.5の回答に寄せられた補足コメントです。 補足日時：2017/02/28 16:46

No.8 回答者： usui-2015 回答日時： 2017/03/01 19:42

先程の更新クエリーの補足です。

下記の様にしても作る事は出来ます。
図を参考にしてやって見て下さい。

この回答へのお礼

色々とご説明いただきありがとうございました。

しばらく開発から離れて別の業務に携わっており御礼が遅くなりすみません。
私の理解力が足りず、キャッチアップする前に担当替えでこの開発から外れることになり、色々ご説明いただいたことを活かしきれずに終わりとなります。

お礼日時：2017/06/07 16:12

No.7 回答者： usui-2015 回答日時： 2017/03/01 19:36

請求書の最終営業日の更新クエリーは下記の様になります。

これをクエ
リーのsqlビューでコピペして下さい。

UPDATE 最終営業日 INNER JOIN 請求書 ON 最終営業日.計上年月 = 請求書.計上年月 SET 請求書.最終営業日 = [最終営業日].[最終営業日];

後は、このクエリーをナビゲーションウインドウで開くか。若しくは、
マクロでクエリーを作ってからマクロから開くか。または、フォームを
作って格好良く作るかは好みの問題です。

これが最終の形になります。なお、ここで折角作ったリレーションシッ
プと言うのは、ここでは使っていません。と言うのはあなたの返事が無
いのでこれ以上は説明は出来ません。

リレーションシップを作った目的と言うのは、今回の様にある日を入力
した後での一括の最終営業日のクエリーでの更新ではなくて。日々の
データエントリーの中で、ある日を入力した段階で直に、フォームの上
で最終営業日を自動入力する為に有ります。

然し、未だリレーションシップが出来たのか分からない段階でのこの段
階と言うのは無理です。然し、リレーションシップが無い状態でも今回
の更新クエリーで問題は解決をします。

これが最後の答えになります。これで、最初の問題は解決します。尚、
リレーションシップを用いたフォームでのデータエントリーの時に直に
最終営業日を自動入力させると言う方法は、何回も言う様に先ずはリ
レーションシップが出来た後の話ですので。

何も無いと言う事で、リレーションシップと言うのは出来てないと見な
して私の説明はこれで終らせて頂きます。

No.6 回答者： usui-2015 回答日時： 2017/02/28 17:26

有難う御座います。

では、私が思って居たとおりの回答でよろしいと言
う事で先にお話をさせて頂きます。ExcelからAccessに移行すると言う
事はデータを蓄積をすると言う事では理にかなっています。

私も現役の頃は大体100万件位迄はAccessでも十分に対処は出来ていま
した。然し、それ以上は無理な様ですが。然し普通はそれ位有れば十分
に対処は出来ると思います。確か、容量的には2GB迄と思っています
が。若しかしたら最新のAccessと言うのはもっと性能が上がっているの
かも知れません。

リンクテーブルを上手く使えば確か1000万件位迄なら何とか運用は出来
ると思います。業務では普通に100万件と言うのは有り得る話です。

さて、ここから先のリレーションシップを作るのは、初めての方は難し
いのかも知れませんが。その時はまた質問をして下さい。それが出来な
いと、先に話をするのも難しいので。

その時をお待ちしています。

No.5 回答者： usui-2015 回答日時： 2017/02/28 16:26

回答を送信してから気が付きましたが。

これと言うのは、単純にExcel
ならvlookup関数でも出来る筈ですが。単純にAccessでやりたかっただ
けなんでしょうか。

だから、ここではExcelでも出来る事を何故にAccessなのかと言う所で
私の最終営業日に関しての勘違いと言うのが有る様な気がして何か、恥
ずかしい。

今回の最終営業日と言うのは、無条件にその年月が与えられれば一意に
一つの日付しか与えられないと思って作成していますので。もしも、こ
の最初の私の想定がずれていたら。今回私が送った回答は棄却して下さ
い。

余り、変な回答をして迷惑するのも嫌なので。それで、会っていると言
う事で有れば次からは最終営業日を作るための更新クエリーの説明に入
りたいと思っています。

No.4 回答者： usui-2015 回答日時： 2017/02/28 16:14

ここでのテーブルと言うのは、Excelの今回は「休業日」リストになり

ます。このシートをAccessのテーブルインポートします。これは、単に
コピペでも大丈夫だと思います。

もし、これが出来るので有ればそれから後の処理は非常に簡単です。こ
こから先は、リレーションシップの説明になります。実は、これこそが
データベースの骨頂と言う所です。

Accessと言うのは、RDBでこの場合のRはリレーションと言う事です。だ
から、Accessで何かしようと思えば。このリレーションシップと言うの
が出来なければ、何も先へとは進みません。

下記に今回のリレーションシップを示します。今回の場合は、テーブル
を「請求書」と「最終営業日」の2つで構成します。

今回の件では正確云々の前に最低限の事しか書けませんので。最初に
「請求書」のテーブルを下記に設定します。
①　id
②　ある日
③　計上年月
④　最終営業日

ここで、
①は、主キーでオートナンバーの型
②は、インデックスで重複有り
③は、インデックスで重複有り
④は、インデックスで重複有り

「最終営業日」のテーブルを下記に示します。
①　計上年月
③　最終営業日

ここで、
①は、主キー
②は、インデックスで重複無し

仮に請求書を示します。
id ある日 計上年月　最終営業日
1 160201 1602
2 160312 1603
3 160512 1605

仮に最終営業日を示します。
計上年月 最終営業日
1601 160120
1602 160225
1603 160330
1604 160425
1605 160522
1606 160625
1607 160720
1608 160823
1609 160925
1610 161023
1611 161125
1612 161229

ここ迄出来たら、リレーションシップを作ります。

まだまだ続きますので、一旦ここで落ちます。
次は、貴方がここ迄出来た事を返事をして下さい。

そこで、この続きを説明します。なお、
最終営業日に関しては、端折って居ますので当面は
理解をする事が大事なので、この通りにやって
出来る事を確認してから後は、随意に変更をして
貰えれば良いので。

然し、それは全てが解決をしてから後の話です。

No.3 回答者： usui-2015 回答日時： 2017/02/27 17:01

最終営業日と言うのは、プログラムで求めると言う事でしょうか。

ここでは、その最終営業日と言うのは、もうAccessとは関係無し
にExcelでも出来ると言うので有れば、Excelで答えを出してそれを
Accessのテーブルにインポートをすれば良いと思います。

最終営業日の計算と言うのは、AccessとかExcelとかは関係は有
りませんから。最終営業日と言うのは、その時々の影響と言うのが
当然出て来ます。例えば祝日に関してはその年に決めると言う
祝日も有るからです。

だから、その部分に関しては別途プログラムで決めるのではなくて
予めデータとしてテーブルを用意をしてた方が早いと思います。

若しも、そのテーブルが用意さえしてあれば。後の処理は、
リレーションシップを作ってさえいれば。簡単にクエリーの内部結合
で最終営業日と言うのは決まります。

この件に関しては、実は今はテーブルとしては、ある日は有ったとして
も最終営業日と言うのは、空白になっていると思いますが。もしも、
ここでリレーションシップを設定していると、フォームで有る日を
入力した段階で直に、最終営業日と言うのが自動的に入る様になりま
す。その方がメンテナンスの関係で良いのではないでしょうか。

その時には今の更新項目の関数の値では無くて、そのままの一側
の基本テーブルの値を結合するだけです。

先ずは、ここで私は最終営業日に関しては、既に予めデータとして
テーブルに値が保存されていると仮定をします。若しも、簡単に
Excelで書けると言うのであれば、それをここに例示してもらえれば、
それを参考にしてのAccessなりのvbaと言うのは出せると思います。

それ以上はお答えは出来ません。やり方に関しては、自分で決めて
下さい。私としては、既にテーブルに最終営業日が保存をしている
と言う前提での回答になります。

もしも、それが駄目と言うので有れば、私の回答はこれで終わりです。

No.2 回答者： usui-2015 回答日時： 2017/02/27 14:49

全て、内容に説明が無いので仮の話として説明をします。

ここでは、テーブル名を請求書とします。

その中に、ある日と言うのが有るのでしょう。ここでは、便宜上請求書
テーブルの中のある日に既にデータが入っていると仮定します。

そのある日から、最終営業日を決めるクエリを作ると言う事でしょう
か。それなら、更新クエリーを作ります。

更新クエリーは下記の様になります。
UPDATE 請求書 SET 請求書.最終営業日 = get([ある日]);

これを、クエリデザインで開いてsqlのビューにしてコピペして下さ
い。この後に、標準モジュールに関数としてgetを作ります。

方法としては、
①　dateaddを使って1月後の月の値を得る。
②　その月の最終日を得る。
③　その月の最終日の営業日を繰り返し文を使って得る。
④　得られた日付を、関数の戻り値として返す

これだけです。先ずは、ここ迄を理解をしてから。次の関数の説明にな
るとは思います。

余り先へ先へと言っても、順番を理解をしない事にはさきばしっても頓
挫をするだけですから。一旦、ここで止めておきます。

又、返事を下さい。関数の中身に付いては次の機会にしたいと思います。

No.1 回答者： usui-2015 回答日時： 2017/02/25 05:08

有る日と言うのは、ユーザーが日付を入力した値と言う事でしょうか。

それならば、ユーザーが日付を入力したイベントでのBefore_updateの
処理でのvbaを記述すれば良いと言う事になります。

この場合は特にレコードソースに付いては限定をしていないので非連結
のテキストボックスとなります。

vbaとしては、
①　入力した日付を元にして、今月の値を得る
②　①の値をDateAddを使って来月の値を得る
③　来月の値の最終日を得る
④　最終日の曜日を得る
⑤　来月の値が12の場合は、最終休日として29を返えす
⑥　12で無い場合は、休日判定をして休日になる迄繰り返し判定をする
⑦　最後の休日を返して繰り返しが終了する
⑧　最後の休日から1日を引いた値を最終営業日として返す
⑨　その値を最終営業日の非連結のテキストボックスに値を代入する

上記の手順のvbaを作れば良いかと思います。

具体的なvbaとしては、質問者の返事を見てからにします。
--------------------------
上記は、特に日付に関しては特に説明が無かったので私が勝手に判断を
しての話です。

然し、質問にはクエリとして考えている様なので。予めデータとしてそ
の日付がテーブルに保存をしていると言う事で有れば。ここでは、クエ
リを作ります。

その時のクエリは更新クエリを作ります。フィールドには更新される項
目をレコードの更新はここに標準モジュールの関数名を入れます。この
関数名の所に先程の手順を入れたvbaが入ります。

以上は、クラスモジュールにするのか、標準モジュールにするのかの違
いは有りますが。vbaの中身その物は同じです。