ACCESSでは時間を入力するデータベースを作成することが良くあります。
勤怠管理データベースで勤務時間を入力したり、生産管理データベースで作業時間を入力したりといったケースです。
こうしたデータベースでは、日ごとに入力した時間を集計して月間の時間を出すのですが、ACCESSでの時間の集計にはコツが必要です。
今回は、ACCESSで時間を集計する方法をご紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
日付/時刻型のデータは単純に合計できない
ACCESSで時間を入力するためには、日付/時刻型のデータを利用します。
テーブルのデザインビューでデータ型を指定する際に日付/時刻型を選択すれば、自動的に時刻として認識されます。
しかし、このデータはあくまで時刻であって時間ではありません。
何が違うかというと、時刻なので1日24時間が上限なのです。
そのため、データを合計した際に24時間を超えてしまうと正しく集計ができないのです。
例として以下のテーブルを準備しました。
単純に時間だけを入力するテーブルです。
データシートビューでいくつか時間を入力します。
では、このテーブルの時間を集計してみましょう。
テーブルを基にしたフォームを作成します。
上記のテーブルをレコードソースに設定した帳票型のフォームで、詳細行に時間を一覧表示します。
フォームヘッダーには時間合計という集計用のテキストボックスを作成しました。
コントロールソースは以下のように設定しています。
=Sum([時間])
test_時間テーブルの時間をSum関数で合計しているので、合計は42時間になるはずですよね。
では、フォームビューで結果を見てみましょう。
結果はこのように18時間になってしまいます。
42時間から24時間を引いた残りの時間のみが表示されています。
これでは、勤怠管理や生産管理などで時間を集計したいと思っても、正しく集計することができません。
ACCESSでの時間の集計方法
では、どのように時間を集計すればよいでしょうか。
時間の集計は、いったん時間を数値型データとして保存して集計し、時刻として表示する際には再度時刻に戻す、という処理を行うことで実現できます。
先ほどのフォームを修正すれば、以下のように正しく時間の合計を表示することができます。
具体的な修正方法をご説明します。
クエリで時間を分に変換
まずは入力された個々の時間を分に直します。
そのために、分を計算するためのクエリを新たに作成します。
上記では時間_分という演算型フィールドを作成し、以下のように記述しました。
Hour([時間])*60+Minute([時間])
Hour関数を使って入力された時間の中で「時」部分を取り出し、60倍することで分に直します。
さらにMinute関数で「分」部分を取り出して上記に足します。
これによって「時間」フィールドに入力された時刻型の値を数値型の分に直すことができます。
データシートビューで結果を見てみましょう。
このように、正しく時間を分に変換できていることがわかります。
フォームのレコードソースをクエリに変更
次に、フォームのレコードソースを今回作成したクエリに変更します。
フォームをデザインビューで開き、プロパティシートでレコードソースを先ほど作成したクエリに変更します。
さらにデザインビューで修正をしていきます。
修正点は以下の3点です。
①時間_分
詳細行に時間だけでなく、クエリで時間を分に修正した「時間_分」テキストボックスを作成します。
こちらは計算に利用するだけで表示する必要はないため、非表示設定でも問題ありません。
②分合計
上記で作成した「時間_分」をSum関数で集計します。
ここでは単純にSum関数のみで集計していますが、時間_分が空白になった場合でも正しく集計するのであればNz関数を組み合わせて、
Nz(Sum([時間_分]))
とする方が汎用的です。
③時間合計
分合計を時間に修正します。計算式は上記の画像では見切れていますが、以下のように設定しています。
=Int([分合計]/60) & ":" & Format([分合計] Mod 60,"00")
分を時間に修正するため、分合計を60で割った商をInt関数で小数点以下を切り捨てし、「時間」部分とします。
“:”で区切った後に、「分」部分を分合計を60で割った余りをMod関数で算出します。
この際、単純にMod関数で余りを出すだけですと余りが1桁になって00:1のような表示になってしまうことがあるため、「分」部分を常に2桁で表示するようFormat関数で”00″という形式に変換しています。
これで正しく時間を集計することができます。
上記の式を見て分かるかと思いますが、時間合計は日付/時刻型のデータではなく、テキストとして時間に見えるように作っているだけ、となります。
さらにこの時間合計を時刻型のデータとして扱いたい場合はTimeValue関数で時刻型データに変換して活用することが可能です。
以上、ACCESSで時間を集計する方法をご紹介しました。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に300以上のACCESSデータベースの開発・修正実績のあるはこにわガジェット（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
