MS-Access2000超入門部屋--祝祭日を覗いた「営業日の日数」を計算する


	<HOME　　<お願い事項　　 <Access2002 TOP　　　<Access97 TOP　　　<サイト内検索

MS-Access2000超入門部屋--祝祭日を覗いた「営業日の日数」を計算する

例えばですね・・・。
なんか、ものを注文するとするじゃありませんか。
で、「いつごろ届くのかしら？」って思いますよね。
単に、「ご注文いただいてから10日間でお届けできます」っていうことなら、「注文日+10」がお届け日になるわけです。
でも、「ご注文いただいてから10日間（ただし土日は除く）でお届けできます」っていう場合は、どうやって「お届け日」を出したらいいんでしょう？

こういう計算をしないといけないことって、たまにあるんじゃないかと思います。
土日がお休みの会社で、実際の「営業日」だけで日付を数えたい場合とか。。。
今日は、そんな時役に立ちそうな考え方、数え方をひとつ、ご紹介しようかなと思います。もちろん、ケースバイケースでやり方はいろいろですけれど、まあまあつかえるんじゃないかと思いますよ。

まずはー・・・あんまりフォームとかレポートとか、見てくれの部分にとらわれずに、テーブルとクエリだけでしっかりデータの出力をやりとげてみましょう。

こんな感じのフォームがあって、ここからいつも注文の入力をします。

このフォームのレコードソースになっているのが主にこんなテーブル。↓
こんな感じのテーブルに、日々、いろんなところからの注文のデータが入ってくるとします。

いろんな商品があるんですが、どの商品も［注文日］から4日でお届けできるとします。
5月2日に、ご注文がありました。単純に考えると、5月2日+4日で、5月6日がお届け日になりますが、ゴールデンウィーク真っ最中です。5月3日、4日、5日、6日はお休み。そこで、実際のお届け日は5月2日に4足して、5月10日と出したいのです。
大元になっているテーブルは、こんな感じの構造になってます。まあ、フィールドの数は練習用なのでいたって少ないですけど、要するに「注文1回ごとに1レコードたまっていくような構造のテーブル」というわけですね。んでもって、

商品名や単価を知るために、別に「商品の情報をいろいろ入れておくテーブル」も作ってあります。
まあ、これは今回はあってもなくてもよいですが、よかったら作っといてください。

この1レコードそれぞれで、「注文日に4足してお届け予定日を出したい」わけです。
はっきりいって無理です。できません。

土日くらいならなんとかわかりそうなもんですけど、Accessには、日本でいつといつが祝日なのかとか、そんなことわかんないですよ。
だから無理です。

・・・これではお話が終わってしまうので、なんとか実現するための下準備に、「カレンダ」の役割を担うテーブルを用意しましょう。
めんどうですけど、必ず必要です。
1年分とか2年分とか作っちゃっとけばよいと思いますよ。

1年365日、その日が休日か営業日かを知ることができるようなテーブルを作っておくのです。曜日はもしかしたらいらないかもしれないですけど、オマケで曜日のフィールドも作っちゃいました。
今回のような場合は、多分、数値型のフィールドを設けて、営業日は0、休日を1にしておくと、簡単かもしれません。

では、クエリを作ります。

［注文テーブル］と、もしあれば、商品の情報が入ってるテーブルも選んで、その商品の単価や商品名を参照できるようにしてあげてください。でも「商品のテーブル」の方は、今回はオマケです。あくまでも［注文テーブル］に入ってきたデータをもとにして考えますので、「まだちょっとクエリって自信がないな・・・」という方は、［注文テーブル］だけで練習しましょう。「商品のテーブル」の方は、あってもなくってもどっちでもいいんだ、くらいの勢いで、焦点絞ってやってみましょう。

まずは、［注文番号］と［注文日］を選んどきましょう。計算式としては［注文日］だけで事足りるんですけど、いずれ［注文番号］も必要になりますよね。「その注文番号で注文した品物は、いつ届くのか」っていうことを知るために、このクエリ作ってるんですもんね。

手始めに、単純に「注文日の4日後」の日付を出してみます。列名を・・・「お届け日」とでもしましょうか。

出ます？

でも、これじゃダメなんですよね。5月5日は営業日じゃないので、お届けはできないです。
そこで登場するのがさっき用意したカレンダーのテーブル。でも、今回はDsumという関数でひっかけて使います。

これで、「カレンダという名前のテーブルの休日フラグというフィールドの中の値を合計した答えを出す」という式になります。休日の場合は1ってなってますからね。全部足せば、休日の数が出てくるって訳です。
列の名前は「休日」にしてみました。

これだと、［カレンダ］テーブルの中の休日全部を数えることになりますね・・・まあ、110日もあるのね。でももっと休みたいと思うのはなぜかしら。

Dsum関数の中で、条件をつけてやりましょう。

ちょっと複雑な書き方になるので、条件の書き方に慣れてない方は、いっしょにゆっくりやってみましょう。
とりあえずこんな風に書いてみてください。

休日: DSum("休日フラグ","カレンダ","日付 between #2001/5/1# and #2001/5/31#")

まず、日付時刻型のフィールドを条件にしたい場合は、半角の＃で囲んで「日付時刻型だよ」ということを教えてあげないといけないんですよね。
「カレンダというテーブルの中の、日付という名前のフィールドの中の値が5/1から5/31までの間で、休日フラグの値を合計しなさい」という命令になります。
5/1から5/31までの間で、休日フラグを1にした日が10日あるようですね。
ここまで、よろしいですか？？

Between ～andのところの書き方、ちょっと複雑になりますんで、こういう式を書くのがちょっと苦手・・・という方は、とりあえずここのトコまでの式をじっくり眺めておいてくださいね。

さあてー。で、どうすればいいかというと？？？
もし、注文日が5月1日だったとしたら、単純計算で出せるお届け日は5月1日です。［カレンダ］テーブルの中で、5月1日から5月5日までの間に、休日が何日あるか数えて、その分上乗せしてあげないといけないわけなんですよね。

まずは、「休日が何日あるか」数えましょう。

休日: DSum("休日フラグ","カレンダ","日付 between #" & [注文日] & "# and #" & [注文日]+4 & "#")

休日: DSum("休日フラグ","カレンダ","日付 between #2001/5/1# and #2001/5/31#")
　　↑これが基準になります。

　　この中の、2001/5/1と2001/5/31の部分にそれぞれ、［注文日］、［注文日］+4というのが入ればよいのです。

休日: DSum("休日フラグ","カレンダ","日付 between #［注文日］# and #［注文日］+4#")

でも・・・［注文日］というのは、フィールドの名前であって、日付そのものではありませんよね。なので、このままでは、「中カッコ注文日中カッコ閉じ」という文字の並びを探してしまいます。なので、この部分だけ、”　”の外に出さないといけないのですよね。
条件式のトコだけ見ますね。

"日付 between #［注文日］# and #［注文日］+4#"
↑これを

"日付 between #” ［注文日］ “# and #” ［注文日］+4 “#"
と、5つのパーツに分けるのです。

これで、［注文日］と［注文日］+4はコーテーションの外に出るのです。

でもこれじゃ、Accessが困ってしまいます。ひとつのフィールドグリッドに、5つもバラバラの式が入っていては、何をしていいのかわかってくれません。
そこで、5つのパーツを、半角の＆でつなげます。

"日付 between #” & ［注文日］ & “# and #” & ［注文日］+4 & “#"