microsoftのACCESSで勤務管理ということで、前回はACCESSの基本的なところを書いてみました。
今回は、システムをどのような仕様に仕上げていくのかを考えてみたいと思います。
システムの仕様を考える
まずは、最終的な形を考えてみます。
- 日ごとに日勤者、夜勤者を表示できる
- 日勤者、夜勤者の変更ができる
- 技師ごとに代休が発生する日の勤務日数を表示できる
- 技師ごとに、代休が発生する日勤、夜勤を一覧で表示できる
- 発生した代休の消化予定を確認できる
をとりあえずの仕様とします。
システムの流れを考える
仕様ができたところで、システムの大きな流れを考えてみます。
- カレンダーの作成
- 日勤、夜勤の順番を決める
- 設定開始日を決めて順番に技師を割り当てていく
- 割り当てたものを1行一日の形式で表示する
- 技師ごとに代休発生日を表示
- 代休の消化予定日を登録
という流れで行きたいと思います。
カレンダーの作成
テーブルの作成
カレンダーの作成です。まずは、カレンダーのテーブルを作成します。
メニューバー「作成」タブ内のテーブルをクリックします。フィールドの設定していきます。
まずは、使わないかもしれませんがオートナンバー型のIDというフィールドは作成しておきましょう。続いて、日付／時刻型の日付というフィールド。
後は休みの日には代休をカウントするための「代休」というフィールドを数値型で作成。あと、使わないかもしれませんが翌日が代休の時の為のフィールド「翌日代休」も作成します。
続いて、登録方法を考えます。Excel等で作成してインポートというのがシステム側としては一番楽なのですが、使用者を考えると毎月の祝日と祭日を登録していくのは面倒です。なので以下のようなフォームでの登録方法としてみました。
フォームの作成
見た目にはあまりこだわっていないので、センスがないというのは無しで・・・・・。ちなみに、このカレンダーは1年分登録ができるように371個のトグルボタンを配置しました。トグルボタンとは、OnとOffを指定できるボタンです。もし、休日ならばOn平日ならばOffです。
開始日付と終了日付を指定して「作成」ボタンを押すことで日付が書かれたボタンが表示され、休日なら自動でボタンがOnになる仕様です。休日、祭日が間違っていなければ「登録」ボタンでテーブルに登録を行います。
それでは、フォームを作成していきましょう。
まずは、トグルボタンを配置していきます。トグルボタンは上のアイコンになります。日曜日から順に土曜日までを1行になるように7個配置します。
その後は、一行丸ごと選択してコピー＆ペーストで複製していきます。
その際、トグルボタン名が「トグル152」「トグル153」のように数字が連番になるように注意してください。
あと、配置したトグルボタンの上に曜日をラベルで配置しておくとわかりやすいです。
終わりましたら続いて、カレンダーを作成する日付と終了する日付を指定します。これはコンボボックスで作成します。
コンボボックスはデザインタブ内の上のアイコンになります。
配置が完了したらプロパティーを開いて書式を「日付（L)」とします。これを終了日も同じように作成します。
続いて、作成ボタンと、登録ボタン、そしてメニューボタンを作成します。
メニューボタンは上の画像のアイコンになります。
3つ配置します。動作は日ほど設定しますのでとりあえずは、配置だけにしておきます。
配置したら、ボタンの上の表題を「作成」、「登録」、「メニュー」としておいてください。
とりあえず、フォームの作成は完了です。保存しましょう。名前に関してはのちにVBAでコードを書いていくのでわかりやすいように「F_calender」としましょう。「F_」はフォームという意味です。のちに組むクエリなのでは「K_」とルールを決めて名前を付けると日にわかりやすくなります。
VBAでコードを書いていく
それでは、コードを書いてきます。
日付を指定して作成ボタンを作成した際に、トグルボタンに日付を表示するわけですが、初めの日が何曜日かを判定してどのトグルボタンから表示していくかを決めます。また、開始日前のトグルボタンは非表示にしておくとわかりやすいのでまずはその処理から始めていきます。
開始日の曜日の取得
作成ボタンの上で右クリックをします。「イベントのビルド」⇒「コードビルダー」を選択します。
以下の画面が出てきます。
もし出てこない場合は、ボタンのプロパティーで「イベント」タブ「クリック時」の［埋め込みマクロ］を削除してください。
ボタンを押したときに、下の画面の
「Private Sub コマンド2＿Click()」から「End Sub」までのコードが実行されることになります。
まずは、開始日の曜日を判定するところから始めます。日付の曜日判定にはWeekday()関数を使います。カッコ内に日付を入れることで曜日が数字として返ってきます。返ってくる数値は曜日によって以下の表のようになります。
| 日曜日 | １ |
| 月曜日 | ２ |
| 火曜日 | ３ |
| 水曜日 | ４ |
| 木曜日 | ５ |
| 金曜日 | ６ |
| 土曜日 | ７ |
変数startdayとして開始日の曜日を取得します。
startday = Weekday(Me!day_start) – 1
私は、開始日コンボボックス名を「day_start」と変えてしまったので、変更していない方は
startday = Weekday(Me!コンボ1) – 1
に様に、開始日のコンボボックスの名前欄に書かれている名前に書き換えてください。
startday = Weekday(Me!day_start) – 1の「-1」は何をしているのかというと
設置したトグルボタンの始まりが「トグル0」と０から始まっていると思うので-1をしています。
開始日の曜日が取得できたのでトグルボタンの開始日以前のトグルボタンを非表示にする作業をしたいと思います。とりあえず、日曜日から土曜日までのトグルボタンをすべて非表示にします。この作業はfor文を使って繰り返していきます。
|
1 2 3 4 5 |
Private Sub コマンド2_Click() For i = 0 To 6 Me("トグル" & i).Visible = False Next i End Sub |
いざ、フォームを表示して作成ボタンをクリックしてみてください。
フォームを表示するのはツールバーの「ホーム」タブの一番左。フォームビューを選択します。
一番上の行のトグルボタンが消えているのが確認できます。
それでは、曜日によって表示するようにしましょう。先ほど取得した曜日の数字がトグルボタンの数字以上であればトグルボタンを表示するコードを書いていきます。
先ほどのコードに以下を追加します。
|
1 2 3 4 5 |
For i = 0 To 6 If startday <= i Then Me("トグル" & i).Visible = True End If Next i |
いかがでしょうか？
きちんと木曜日からトグルボタンが表示されていると思います。
トグルボタンに日付を表示していく
それでは、トグルボタンに曜日を表示させていきましょう。
これもfor文で終了日まで繰り返していきます。開始日から、終了日までの日数を計算します。diff_dayという変数に入れます。
期間を計算する計算式は以下となります
diff_day = DateDiff(“d”, Me!day_start, Me!day_end)
DateDiff(“d”, 開始日,終了日)で日単位での期間が計算できます。
ちなみに、終了日のコンボボックス名をday_endとしてしまっているのでそこは皆さんの環境によって書き換えてください。
それではfor文で書いていきましょう
以下のコードを追加してください。
|
1 2 3 4 5 6 |
diff_day = DateDiff("d", Me!day_start, Me!day_end) For i = 0 To diff_day + 1 Me("トグル" & startday + i).Caption = Format(day_start + i, "mm/dd") next i |
きちんとトグルボタンに日付が表示されたと思います。なお、期間+1日まで日付を表示したのはちょっと分け合ってですが、そこは後々書きます。
休日設定
日付の表示までできましたが、いちいち休日の日のボタンを押していくのは面倒なのでプログラムで解決してしまいましょう。
まずは、日曜日の設定ですが、初めに出てきたweekday関数で簡単に解決できます。
|
1 |
If Weekday(day_start + i) - 1 = 0 Then Me("トグル" & startday + i).Value = True |
当院は、第2土曜日が休診日になるのでその設定をします。
第2土曜日の判定をするのは先ほどのweekday関数で戻り値が土曜日である６であることと、第2週目であることで判定します。
第2週目ということを判定するのは日付を7で割った答えが2であればいいのですが、単純にやってしまうと間違ってしまいます。
まずは、日付に6を足してあげる必要があります。例えば、1日が土曜日であった場合、7で割ると０になってしまい、次の週の8日が1週目となってしまいす。そのため、日付に６を足してあげて7で割ることで何週目かを求めることができます。
第2土曜日を判定するコードは以下となります。
|
1 |
If Weekday(day_start + i) - 1 = 6 And (Day(day_start + i) + 6) \ 7 = 2 Then Me("トグル" & startday + i).Value = True |
次に各月での祝祭日の設定になります。これは、月によって処理を変えていきますので「select case」の関数によって処理を変えていきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
'各祝祭日設定 Select Case Month(day_start + i) Case 1 If Day(day_start + i) = 1 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 2 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 3 Then Me("トグル" & startday + i).Value = True If Weekday(day_start + i) - 1 = 1 And (Day(day_start + i) + 6) \ 7 = 2 Then Me("トグル" & startday + i).Value = True If Weekday(day_start + i) - 1 = 6 And (Day(day_start + i) + 6) \ 7 = 2 Then Me("トグル" & startday + i).Value = True Case 2 If Day(day_start + i) = 11 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 23 Then Me("トグル" & startday + i).Value = True Case 3 If Day(day_start + i) = 21 Then Me("トグル" & startday + i).Value = True Case 4 If Day(day_start + i) = 29 Then Me("トグル" & startday + i).Value = True Case 5 If Day(day_start + i) = 3 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 4 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 5 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 15 Then Me("トグル" & startday + i).Value = True Case 7 If Weekday(day_start + i) - 1 = 1 And (Day(day_start + i) + 6) \ 7 = 3 Then Me("トグル" & startday + i).Value = True Case 8 If Day(day_start + i) = 11 Then Me("トグル" & startday + i).Value = True Case 9 If Weekday(day_start + i) - 1 = 1 And (Day(day_start + i) + 6) \ 7 = 3 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 22 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 23 Then Me("トグル" & startday + i).Value = True Case 10 If Weekday(day_start + i) - 1 = 1 And (Day(day_start + i) + 6) \ 7 = 2 Then Me("トグル" & startday + i).Value = True Case 11 If Day(day_start + i) = 3 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 23 Then Me("トグル" & startday + i).Value = True Case 12 If Day(day_start + i) = 29 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 30 Then Me("トグル" & startday + i).Value = True If Day(day_start + i) = 31 Then Me("トグル" & startday + i).Value = True End Select |
いかがでしょうか？きちんと休日、祭日の設定ができていますね。
もし、間違っていた場合はトグルボタンで作成しているので変更が可能になっています。
ちなみに、終了日以降のトグルボタンの非表示は面倒になってしまったので省略してしまいました……
テーブルへの登録
カレンダーの表示は完了しましたが、テーブルへの登録が完了していませんのでこれからはカレンダーへの登録をしていきます。
コードとしてはSQL文を使って登録をしていきます。
テーブルへのデータ登録を行うSQL文はINSERT文を使います。
INSERT INTO 登録するテーブル ([フィールド1],[フィールド2],[フィールド3]) VALUES(値1,値2,値3）
これを変数に入れ、VBAで実行する形となります。
テーブル「calender」への登録は日付、代休、翌日代休を登録していきます。
登録していく日付はトグルボタンに表示されている日付を登録し、トグルボタンがTRUEの場合は、holidayという変数を1にします。翌日の代休に関しては次のトグルボタンの状態を見て判断をします。
なお、土曜日に当直の場合0.5日分の代休が発生するので、土曜日の場合は初めにholidayの変数に0.5を代入し、第2土曜日であればholidayに１に変更するという作業にします。
|
1 2 3 4 5 6 7 8 |
'当日の代休設定 If Weekday(day_start + i) = 7 Then holiday = 0.5 If Me("トグル" & startday + i).Value = True Then holiday = 1 '翌日の代休設定 If Weekday(day_start + i) + 1 = 7 Then next_holiday = 0.5 If Me("トグル" & startday + i + 1).Value = True Then next_holiday = 1 |
そうしましたらSQL文を作成、実行します。
|
1 2 3 |
'sql実行 sql_insert = "INSERT INTO calender ([日付],[代休],[翌日代休]) VALUES (#" & Me!day_start + i & "# , " & holiday & " , " & next_holiday & ")" DoCmd.RunSQL sql_insert |
上記コードをfor文を使って設定したい日数分だけ繰り返します。
なお、最終日は翌日の代休が分からないのでわざと1日カレンダーの作成を行いました。そして、テーブルへの登録を行う際に登録回数を1回減らすことで登録日数分にします。
いかに登録ボタンが押された時のコードを書きます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Private Sub コマンド397_Click() '登録ボタン Dim startday As Integer DoCmd.SetWarnings WarningsOn:=False startday = Weekday(Me!day_start) - 1 diff_day = DateDiff("d", Me!day_start, Me!day_end) holiday = 0 next_holiday = 0 For i = 0 To diff_day insert_day = day_start + i today_week = Weekday(day_start + i) '当日の代休設定 If Weekday(day_start + i) = 7 Then holiday = 0.5 If Me("トグル" & startday + i).Value = True Then holiday = 1 '翌日の代休設定 If Weekday(day_start + i) + 1 = 7 Then next_holiday = 0.5 If Me("トグル" & startday + i + 1).Value = True Then next_holiday = 1 'sql実行 sql_insert = "INSERT INTO calender ([日付],[代休],[翌日代休]) VALUES (#" & Me!day_start + i & "# , " & holiday & " , " & next_holiday & ")" DoCmd.RunSQL sql_insert holiday = 0 next_holiday = 0 Next i End Sub |
動作確認
それでは、動作確認をしてみましょう。
フォームビューで開いて、開始日と終了日をしていします。
その後、作成ボタンを押してください。
休日設定が間違っていなければ登録ボタンを押すことでテーブルに登録されます。
calenderのテーブルを開いてきちんと登録されているか確認してみてください。
いかがでしたか？
次回は当直順を決め、それを順番に埋めていき当直表を作成する作業を紹介したいと思います。
お疲れ様でした。
