アルバイトの就業時間をアクセスで管理しております。
午前10:00~翌日朝6:00まで営業しております。
やりたいことは、夜22:00~翌朝の29:00(朝5時)までの時間を月ごとに集計をして
誰が深夜何時間働いているかを調べたいです。
22時から29時は時間給が高くなるのです。
タイムカードは、出社時と退社時に押します。(15分単位で切り捨てたいです。)
氏名 日付 出社 退社 調べたい時間
Aさん 10月10日 20:00~25:20 3時間15分
Bさん 10月10日 21:00~29:10 7時間
Aさん 10月11日 22:00~26:05 4時間
Bさん 10月13日 18:13~28:23 6時間15分
Aさん 10月14日 21:16~30:00 7時間
22:00~29:00までの時間を調べて、その月での集計をしたいです。
Aさん 14時間15分
Bさん 13時間15分
クエリの集計機能が使えるのかと思ったのですが、時間の計算ができませんでした。
説明不足な点も多々あるかと思いますが、ご教授頂けたら幸いです。
よろしくお願いいたします。
No.6ベストアンサー
- 回答日時:
#3です
その後、いろいろと検証等されていると思います。
元テーブルが
氏名 日付 出社 退社
Aさん 2013/10/10 20:00:00 1:20:00
Cさん 2013/10/15 1:20:00 7:00:00
Dさん 2013/10/15 9:20:00 17:30:00
だった場合、
氏名 日付 出社 退社 時間
Aさん 2013/10/10 20:00:00 1:20:00 3.25
Cさん 2013/10/15 1:20:00 7:00:00 3.5
Dさん 2013/10/15 9:20:00 17:30:00 0
(1:20 + 2:00 = 3:20 の 15分単位切り捨て 3:15 → 3.25)
(5:00 - 1:20 = 3:40 の 15分単位切り捨て 3:30 → 3.5)
とするには、クエリの書き方を工夫すれば良いと思います。
例えば、
SELECT 氏名, 日付, 出社, 退社,
CalcNightTimes(日付+出社,日付+退社-(出社>退社)) AS 時間
FROM テーブル名;
出社>退社 なら1日加算する方法ですが、
出社>退社 の比較で True なら -1 になるので、
日付+退社-(出社>退社) で、結果1日加算される事になります。
#3では、出社・退社とも埋まっているものとして動くので、クエリ上で
SELECT 氏名, 日付, 出社, 退社,
CalcNightTimes(日付+出社,日付+退社-(出社>退社)) AS 時間
FROM テーブル名
WHERE 出社 Is Not Null AND 退社 Is Not Null;
と条件指定しても良いかも(元々 Null が無ければ不要ですが)
関数側で Null をはじく様に修正しても良いと思います。
※ 営業時間と勤務時間は違いますよね?
過剰情報になりますが、
TRANSFORM Sum(CalcNightTimes(日付+出社,日付+退社-(出社>退社))) AS 値
SELECT 氏名
FROM テーブル名
GROUP BY 氏名
PIVOT Format(日付,"yyyy/mm");
とすると、クロス集計で深夜時間合計が表示されます。
補足)#3での VBA 記述の説明をしておきます。
22:00 ~ 翌 5:00 ・・・・
これ、考えにくいので、与えられた dtS / dtE 共に 2:00 時間加算しておく事に・・・
これにより、深夜時間帯は 0:00 ~ 7:00 に偏らせる事が出来ます。
その細工をしておいて、
dtS / dtE が同じ日なら、
・ 0:00 ~ 7:00 に dtE が入っていたら、dtE - dtS が深夜時間
・ 0:00 ~ 7:00 に dtS が入っていたら、7:00 - dtS(時刻部分)が深夜時間
dtS / dtE が同じ日でなかったら、
・ 0:00 ~ 7:00 に dtS が入っていたら、7:00 - dtS(時刻部分)が朝方の深夜時間
・ dtE - Int(dtE) ・・・ 0:00 からの差を覚えておいて、それが、7:00 以上なら 7:00 に訂正
・ 上記、朝方と日付が変わった分を加算したものが深夜時間
※ 一見、#3で大丈夫そうに見えたりしますが、
wdt = dtE - Int(dtE)
部分でおかしくなっていく事があります。その条件は、
dtS / dtE が同じ日でなかったら・・・・ の場合で、日付差が 1 を超えた場合・・・
つまり、#3のコードでは、日付差 = 1 を前提としてしています。
dtR = dtR + wdt
この部分を ↓
dtR = dtR + wdt
dtR = dtR + (Int(dtE) - Int(dtS) - 1) * #7:00:00 AM#
とでも変更すれば良さそうです。
ただ、40 時間以上の連続勤務・・・・ どうなんですかね(必要なら修正するってことで)
氏名 日付 出社 退社
Aさん 2013/10/10 20:00:00 1:20:00
Cさん 2013/10/15 1:20:00 7:00:00
のデータで
SELECT 氏名, 日付, 出社, 退社,
CalcNightTimes(日付+出社,日付+退社-(出社>退社)) AS 時間
FROM テーブル名;
するのなら、日付差最大は 1 にしかならないので修正は不要と思います。
氏名 出社 退社
Aさん 2013/10/10 20:00:00 2013/10/11 1:20:00
という持ち方をした時に、考えればいいと思います。
※ と一部、ブログ過去記事の内容を転記してみました。
No.5
- 回答日時:
コードで変換ミスがあったので訂正して再登校します。
> データ型は日付時刻型です。
> 分かりやすくしようと思い、25時などと表現してしまいましたが、
>
> 翌日の朝5時や朝1時の事です。
日付 出社 退社
10月10日 20:00 25:20
ではなく、
日付 出社出社 退社
10月10日 20:00 01:20
というように入力されているということでしょうか。
クエリで式で求めると複雑になるので、標準モジュールにユーザー定義関数を作成します。
'深夜の就業時間を分単位で返す
Public Function NightWorkTime(出社, 退社)
Dim t1 As Date, t2 As Date
Dim m1 As Long, m2 As Long
If IsNull(出社) Or IsNull(退社) Then
NightWorkTime = Null
Exit Function
End If
t1 = 出社
t2 = 退社
If t1 > t2 Then t2 = t2 + 1
If t2 < #10:00:00 PM# Or t1 > #5:00:00 AM# + 1 Then
NightWorkTime = 0
Exit Function
End If
If t1 < #10:00:00 PM# Then t1 = #10:00:00 PM#
If t2 > #5:00:00 AM# + 1 Then t2 = #5:00:00 AM# + 1
m1 = (DateDiff("n", #12:00:00 AM#, t1) \ 15) * 15 '分に変換して15分単位で切り捨て
m2 = (DateDiff("n", #12:00:00 AM#, t2) \ 15) * 15
NightWorkTime = m2 - m1
End Function
集計クエリで、
フィールド / 集計
氏名 / グループ化
年月: Format(日付,"yyyy/mm") / グループ化
深夜就業時間: NightWorkTime(出社, 退社) / 合計
とすれば、社員、月別の 深夜就業時間 が分単位で表示されます。
分単位を何時間何分という表示に変換するには、下記の式で。
Format(深夜就業時間 \ 60,"0時間") & Format(深夜就業時間 Mod 60,"0分")
No.4
- 回答日時:
> データ型は日付時刻型です。
> 分かりやすくしようと思い、25時などと表現してしまいましたが、
>
> 翌日の朝5時や朝1時の事です。
出社 退社
20:00 25:20
ではなく、
出社 退社
20:00 01:20
というように入力されているということでしょうか。
クエリで式で求めると複雑になるので、標準モジュールにユーザー定義関数を作成します。
'深夜の就業時間を分単位で返す
Public Function NightWorkTime(出社, 退社)
Dim t1 As Date, t2 As Date
Dim m1 As Long, m2 As Long
If IsNull(出社) Or IsNull(退社) Then
NightWorkTime = Null
Exit Function
End If
t1 = 出社
t2 = 退社
If t1 > t2 Then t2 = t2 + 1
If t2 < #10:00:00 PM# Or t1 > #5:00:00 AM# + 1 Then
NightWorkTime = 0
Exit Function
End If
If t1 < #10:00:00 PM# Then t1 = #10:00:00 PM#
If t2 > #5:00:00 AM# + 1 Then t2 = #5:00:00 AM# + 1
m1 = (DateDiff("n", #12:00:00 AM#, t1) ¥ 15) * 15 '分に変換して15分単位で切り捨て
m2 = (DateDiff("n", #12:00:00 AM#, t2) ¥ 15) * 15
NightWorkTime = m2 - m1
End Function
集計クエリで、
氏名 グループ化
年月: Format(日付,"yyyy/mm") グループ化
深夜就業時間: NightWorkTime(出社, 退社) 合計
とすれば、社員、月別の 深夜就業時間 が分単位で表示されます。
分単位を何時間何分という表示に変換するには、下記の式で。
Format(深夜就業時間 \ 60,"0時間") & Format(深夜就業時間 Mod 60,"0分")
No.3
- 回答日時:
氏名 出社 退社
Aさん 2013/10/10 20:00:00 2013/10/11 1:20:00
とあった場合、
氏名 出社 退社 時間
Aさん 2013/10/10 20:00:00 2013/10/11 1:20:00 3.25
とするものになります。(「出社」「退社」とも日付/時刻型)
以下の関数を標準モジュールに記述しておきます。
Public Function CalcNightTimes(ByVal dtS As Date, ByVal dtE As Date) As Double
Dim wdt As Date
Dim dtR As Date
dtS = dtS + #2:00:00 AM#
dtE = dtE + #2:00:00 AM#
wdt = Int(dtS) + #7:00:00 AM#
If (Int(dtS) = Int(dtE)) Then
If (dtE <= wdt) Then
dtR = dtE - dtS
ElseIf (dtS <= wdt) Then
dtR = wdt - dtS
End If
Else
If (dtS <= wdt) Then
dtR = wdt - dtS
End If
wdt = dtE - Int(dtE)
If (wdt > #7:00:00 AM#) Then wdt = #7:00:00 AM#
dtR = dtR + wdt
End If
CalcNightTimes = Hour(dtR) _
+ Choose(Minute(dtR) \ 15 + 1, 0, 0.25, 0.5, 0.75)
End Function
クエリでは、SQLビューでみた時
SELECT 氏名, 出社, 退社, CalcNightTimes(出社,退社) AS 時間
FROM テーブル名;
とすれば時間部分が計算されて表示されます。
10月の・・・ で集計するとすれば以下のように記述すれば良いと思います。
SELECT 氏名, Sum(CalcNightTimes(出社,退社)) AS 時間
FROM テーブル名
WHERE 出社 >= #2013/10/1# AND 出社 < #2013/11/1#
GROUP BY 氏名;
なお、
> 15分単位で切り捨てたいです
が、いつの段階かわからないのですが、計算した結果に適用するのであれば上記のままで。
もし、
氏名 出社 退社
Aさん 2013/10/10 20:03:00 2013/10/11 1:20:00
のデータは
氏名 出社 退社
Aさん 2013/10/10 20:15:00 2013/10/11 1:15:00
と解釈してから・・・であれば、以下の記述を処理先頭に追加してください。
If ((Minute(dtS) Mod 15) <> 0) Then
dtS = Int(dtS) _
+ TimeSerial(Hour(dtS) _
, Choose(Minute(dtS) \ 15 + 1, 15, 30, 45, 60) _
, 0)
End If
If ((Minute(dtE) Mod 15) <> 0) Then
dtE = Int(dtE) _
+ TimeSerial(Hour(dtE) _
, Choose(Minute(dtE) \ 15 + 1, 0, 15, 30, 45) _
, 0)
End If
※ たぶん上記で動くと思いますが、不都合あれば修正してください。
No.2
- 回答日時:
出社、退社 のフィールドのデータ型はなんですか。
日付/時刻型だと、29:10 というような24:00以降の表現はできませんので、テキスト型でしょうか。
それが明確にならないと回答できないです。
あと、調べた時間は、月で集計するということですね。
それを考慮すると、計算結果は数値型になるようにすべきですね。
例えば、3時間15分なら、3.25 にするとか。
テキスト型だと、集計できません。
この回答への補足
大変申し訳ございません。
データ型は日付時刻型です。
分かりやすくしようと思い、25時などと表現してしまいましたが、
翌日の朝5時や朝1時の事です。
どうぞよろしくお願いいたします。
No.1
- 回答日時:
FLOOR関数:基準値で該当する値を切り捨てる関数
MAX関数:与えられた数値の中の最大値を選択する関数
MIN関数:与えられた数値の中の最小値を選択する関数
■全体の勤務時間
15分単位の切り捨てなのでFLOOR関数を使います。
=FLOOR(退社-出社,TIME(0,15,0))
■22時から29時の勤務時間
退社時間が22時~29時の間、出社時間が22時以降で計算すればいいので
=FLOOR(MAX(MIN(退社,29/24),22/24)-MIN(MAX(出社,22/24),29/24),TIME(0,15,0))
上記数式で
退社が29時~30時の場合は、29時になります。(MIN関数)
退社が22時以前の場合は、22時になります。(MAX関数)
退社が22時~29時の場合は、そのままの時刻になります。
出社が22時以前の場合は、22時になります。(MAX関数)
出社が29時~30時の場合は、29時になります。(MIN関数)
出社が22時~29時の場合は、そのままの時刻になります。
退社-出社で22時~29時の勤務時間が計算できます。
後は15分で切り捨てを行います。(FLOOR関数)
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・「I love you」 をかっこよく翻訳してみてください
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・昔のあなたへのアドバイス
- ・かっこよく答えてください!!
- ・あなたが好きな本屋さんを教えてください
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
入社?入組?
-
履歴書の職歴
-
臨時職員をした後の履歴書の書き方
-
学校で働いていた場合の職歴の...
-
本日は失礼させて頂きましたor...
-
自分の入社日・退社日を知りたい。
-
郵政のお仕事をしていた方 - 履...
-
同じ会社に出戻りのときは職務...
-
わかりません
-
エクセル 深夜勤務時間の算出方法
-
女性が寿退社するのが一番多い...
-
同じ会社に3度就職したときの履...
-
一身上の都合?それとも健康上...
-
分社した会社へ転籍した場合の...
-
有限会社から株式会社に変更が...
-
仕事は定時の時間までやります...
-
社会福祉法人の職歴
-
Excelで入社・退社年月日を入力...
-
「出社」の反対は?
-
期限付きのバイトをした場合(...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
学校で働いていた場合の職歴の...
-
臨時職員をした後の履歴書の書き方
-
入社?入組?
-
自分の入社日・退社日を知りたい。
-
本日は失礼させて頂きましたor...
-
郵政のお仕事をしていた方 - 履...
-
履歴書の職歴
-
分社した会社へ転籍した場合の...
-
同じ会社に3度就職したときの履...
-
Accessで勤続年数(○年○ヶ月)...
-
一身上の都合?それとも健康上...
-
同じ会社に出戻りのときは職務...
-
一般課程試験
-
試用期間満了時に退職した場合
-
法人に入社(?)した時は何と...
-
仕事は定時の時間までやります...
-
人事、労務で行ってきた業務内...
-
期限付きのバイトをした場合(...
-
会社が吸収合併された場合の履...
-
Excel マクロ1回目の結果反応遅...
おすすめ情報