Access (一般機能)

Accessの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10全般 : Access 2016)
有給休暇の付与日数算出について
投稿日時: 18/10/05 14:23:57
投稿者: たんしお

いつも利用させていただいております。
accsessにて、パートの有給付与日数の算出をしたいと考えておりますが、
どのように設計したらいいのかわからず、質問させていただきました。
 
下記のような条件で有給休暇が付与されます。
付与基準日:基本入社から半年後の付与ですが、勤続年数の長い方は一斉付与になっております。
付与基準:付与日までの12か月分の労働日数を集計して、付与基準表に沿って(勤続年数と労働日数)の
     付与となります。
 
テーブル
@基本情報(社員番号、氏名、入社年月日、付与基準日)
A有給休暇付与基準(勤続年数と労働日数に沿って法律で決まっている付与表を作成)
B給与データ(毎月の勤怠データ取込用になります)
 
クエリ
@基本情報から勤続年数を計算
 
まだ、ここまでしかできておりません。情報が不足しており申し訳ありませんが
アドバイスをいただけると大変助かります。
 
 

回答
投稿日時: 18/10/05 15:02:00
投稿者: Suzu

有給休暇付与基準
https://www.mhlw.go.jp/new-info/kobetu/roudou/gyousei/dl/140811-3.pdf
 
を参考に。。。
 
 
テーブル構造等、詳しい部分が判りませんので、ざっとの流れを。。
 
1.勤続年数算出 (基本情報テーブル より) ※済
    基本情報テーブルに、各人の所定労働日数が必要と思いますが。。
 
2.労働日数算出
     ・給与データ
     ・基本情報テーブル[付与基準日]
         付与基準日から 12ヶ月前 までの 労働日数を算出
 
3.出勤率を算出
 
3.有給休暇付与基準テーブル に 1と2の情報を 条件に、付与日数を参照
 
私なら「有給休暇付与基準テーブル」は
 

ID		:	オートナンバー
週所定日数	:	数値型
所定日数S	:	数値型
所定日数E	:	数値型
勤続年数	:	数値型	(5,15,25 .. 年数×10で入力)
付与日数	:	数値型

こんな感じでしょうか。

投稿日時: 18/10/05 16:06:28
投稿者: たんしお

Suzu様
  
返信ありがとうございます。内容が不足して申し訳ありません。
 知識不足で質問ばかりになりますが、宜しくお願い致します。
  
1.勤続年数算出 (基本情報テーブル より)
    基本情報テーブルに、各人の所定労働日数をが必要致しました。
   →所定労働日数の集計は、クエリでの設定になりますでしょうか
   
2.労働日数算出
     ・給与データ
     ・基本情報テーブル[付与基準日]
           付与基準日から 12ヶ月前 までの 労働日数を算出
   →クエリでの設定になるのでしょうか
     12ヶ月前 までの 労働日数の算出方法等がわからない状況です。
 
3.出勤率を算出
  →8割の出勤率は考慮せず、週5日の方が週3日の所定労働日数しか勤務しない場合、
   付与対象外とせず、週3日での勤続年数に沿った有給日数を付与します。
  
有給休暇付与基準テーブルは、下記のように設定しておりました。
ID : オートナンバー
週所定労働日数 : 数値型
 所定労働日数最小: 数値型
 所定労働日数最大: 数値型
 勤続年数6ヶ月 : 数値型
 勤続年数1年6ヶ月: 数値型
 勤続年数2年6ヶ月: 数値型
 勤続年数3年6ヶ月: 数値型
 勤続年数4年6ヶ月: 数値型
 勤続年数5年6ヶ月: 数値型
 勤続年数6年6ヶ月以上: 数値型

回答
投稿日時: 18/10/09 11:41:28
投稿者: Suzu

引用:
1.勤続年数算出 (基本情報テーブル より)
    基本情報テーブルに、各人の所定労働日数をが必要致しました。
   →所定労働日数の集計は、クエリでの設定になりますでしょうか

この「所定労働日数」は、出勤率を計算する上で必要になるので加えました。
 
3の回答にある様に、出勤率が、週何日勤務したかで良いのであれば、フィールドは必要ないです。
(計算上必要無いと言うだけで、法律的にその計算方法で良いのかの確認は必要と思います)
 
 
引用:
2.労働日数算出
     ・給与データ
     ・基本情報テーブル[付与基準日]
           付与基準日から 12ヶ月前 までの 労働日数を算出
   →クエリでの設定になるのでしょうか
     12ヶ月前 までの 労働日数の算出方法等がわからない状況です。

・給与データはどんなフィールド構成なのでしょうか。
 
基本的には、
 付与基準日 に対し DataAdd関数 を使い、「1年前」「1日前」 の日付は計算できます。
 なのでその算出した日付を期間の条件として 出勤日 の合計を計算すれば良いです。
 
 
引用:
3.出勤率を算出
  →8割の出勤率は考慮せず、週5日の方が週3日の所定労働日数しか勤務しない場合、
   付与対象外とせず、週3日での勤続年数に沿った有給日数を付与します。

であれば、出勤率計算は不要ですね。
でも、週○日労働 或いは 1年間の 労働日数 の計算は必要ですね。
 
 
引用:
勤続年数6ヶ月 : 数値型
 勤続年数1年6ヶ月: 数値型
 勤続年数2年6ヶ月: 数値型
 勤続年数3年6ヶ月: 数値型
 勤続年数4年6ヶ月: 数値型
 勤続年数5年6ヶ月: 数値型
 勤続年数6年6ヶ月以上: 数値型

このテーブルの形だと、取得する列が変わる事になりますよね?
一般機能の関数や、SQLのみで実現できますか?
VBAでなら簡単ですが。。

投稿日時: 18/10/10 14:17:57
投稿者: たんしお

Suzu様
 
返信ありがとうございます。
 
・給与データはどんなフィールド構成なのでしょうか。
 
以下のような構成となっております。
フィールド データ型
個人コード:数値型
氏名   :短いテキスト
所属名  :短いテキスト
労働日数 :数値型
欠勤日数 :数値型
労働時間 :数値型
時間外総計:数値型
土祝出時間:数値型
休出時間 :数値型
深夜残業 :数値型
総労働時間:数値型
年休付与 :数値型
年休日数 :数値型
年休残日数:
...
 
労働日数と年休日数(取得)を合計した日数が月の労働日数となるため、
クエリで労働日数の合計を出しております。
 

引用:
勤続年数6ヶ月 : 数値型
 勤続年数1年6ヶ月: 数値型
 勤続年数2年6ヶ月: 数値型
 勤続年数3年6ヶ月: 数値型
 勤続年数4年6ヶ月: 数値型
 勤続年数5年6ヶ月: 数値型
 勤続年数6年6ヶ月以上: 数値型

このテーブルの形だと、取得する列が変わる事になりますよね?
一般機能の関数や、SQLのみで実現できますか?
VBAでなら簡単ですが。
 
付与基準表を基に同じ構成図で作成してしまったのですが、
一般機能の関数や、SQLを理解できてない状態です。
教えていただけますでしょうか
 
 

回答
投稿日時: 18/10/10 15:03:37
投稿者: Suzu

引用:
・給与データはどんなフィールド構成なのでしょうか。
 
以下のような構成となっております。
フィールド データ型
個人コード:数値型
氏名   :短いテキスト
所属名  :短いテキスト
労働日数 :数値型
欠勤日数 :数値型
労働時間 :数値型
時間外総計:数値型
土祝出時間:数値型
休出時間 :数値型
深夜残業 :数値型
総労働時間:数値型
年休付与 :数値型
年休日数 :数値型
年休残日数:
...
 
労働日数と年休日数(取得)を合計した日数が月の労働日数となるため、
クエリで労働日数の合計を出しております。

 
上記は、年間の実績でしょうか。
 
引用:
B給与データ(毎月の勤怠データ取込用になります)

としたら、年月を表わすフィールドがあるはずですが。。。
 
どちらにしても基準日の一年前から基準日前日までの出勤数は計算できませんよね?
基準日が、○月 1日 という様に、1日 に限定されているのであれば、月毎のデータであれば計算可能でしょう。
 
 
Access 云々は 別として、
まずは、手計算でもなんでも構いません、データを揃えて、目的の値の算出を行いましょう。
手動で計算が可能なデータが揃って初めて計算の定型化/自動化が可能です。
 
人が計算ができない状態なのであれば、コンピューターも計算不能です。

投稿日時: 18/10/10 15:20:50
投稿者: たんしお

Suzu様
 
データの整理ができてない状態の質問となってしまい、申し訳ありません。
 
[quote="Suzu"]

引用:
・給与データはどんなフィールド構成なのでしょうか。
 
以下のような構成となっております。
フィールド データ型
個人コード:数値型
氏名   :短いテキスト
所属名  :短いテキスト
労働日数 :数値型
欠勤日数 :数値型
労働時間 :数値型
時間外総計:数値型
土祝出時間:数値型
休出時間 :数値型
深夜残業 :数値型
総労働時間:数値型
年休付与 :数値型
年休日数 :数値型
年休残日数:
...
 
労働日数と年休日数(取得)を合計した日数が月の労働日数となるため、
クエリで労働日数の合計を出しております。

 
上記は、年間の実績でしょうか。
 
→こちらは、毎月の取込用の給与データの構成となっております。
 この元データの給与データは、日付をもっておりません。
 今、テーブルデータを見ており、給与データが累積された場合に日付の判断ができないことに
 気付きました。取込用前に追加したいと思います。
 

回答
投稿日時: 18/10/10 17:04:20
投稿者: Suzu

基本情報

ID	個人コード	氏名	入社日	付与基準日
1	23342	佐藤太郎	1998/4/1	10月1日
2	23345	中村花子	1991/4/1	10月1日
3	23346	中村花子2	2018/4/1	10月1日

 
 
有給休暇付与基準
ID	週所定日数	所定日数S	所定日数E	勤続年数	勤続月数	付与日数
1	4	169	216	5	6	7
2	4	169	216	15	18	8
3	4	169	216	25	30	9
4	4	169	216	35	42	10
5	4	169	216	45	54	12
6	4	169	216	55	66	13
7	4	169	216	65	78	15
8	3	121	168	5	6	5
9	3	121	168	15	18	6
10	3	121	168	25	30	6
11	3	121	168	35	42	8
12	3	121	168	45	54	9
13	3	121	168	55	66	10
14	3	121	168	65	78	11
15	2	73	120	5	6	3
16	2	73	120	15	18	4
17	2	73	120	25	30	4
18	2	73	120	35	42	5
19	2	73	120	45	54	6
20	2	73	120	55	66	6
21	2	73	120	65	78	7
22	1	48	72	5	6	1
23	1	48	72	15	18	2
24	1	48	72	25	30	2
25	1	48	72	35	42	2
26	1	48	72	45	54	3
27	1	48	72	55	66	3
28	1	48	72	65	78	3

 
 
給与データ
ID	年月	個人コード	氏名	労働日数	年休付与	年休日数	年休残日数
1	201710	23342	佐藤太郎	20	20	0	20
2	201711	23342	佐藤太郎	19	0	1	19
3	201712	23342	佐藤太郎	18	0	2	17
4	201801	23342	佐藤太郎	20	0	0	17
5	201802	23342	佐藤太郎	17	0	3	14
6	201803	23342	佐藤太郎	19	0	1	13
7	201804	23342	佐藤太郎	17	0	3	10
8	201805	23342	佐藤太郎	15	0	5	5
9	201806	23342	佐藤太郎	20	0	0	5
10	201807	23342	佐藤太郎	20	0	0	5
11	201808	23342	佐藤太郎	19	0	1	4
12	201809	23342	佐藤太郎	18	0	2	3
13	201810	23342	佐藤太郎	20	20	0	23
14	201709	23345	中村花子	20	0	0	0
15	201710	23345	中村花子	19	20	0	20
16	201711	23345	中村花子	18	0	1	19
17	201712	23345	中村花子	17	0	2	17
18	201801	23345	中村花子	19	0	0	17
19	201802	23345	中村花子	13	0	3	14
20	201803	23345	中村花子	18	0	1	13
21	201804	23345	中村花子	16	0	3	10
22	201805	23345	中村花子	14	0	5	5
23	201806	23345	中村花子	19	0	0	5
24	201807	23345	中村花子	19	0	0	5
25	201808	23345	中村花子	18	0	1	4
26	201809	23345	中村花子	17	0	2	3

 
だとして
 
以下を参考にどうぞ。
 
SELECT 個人コード, SUM(労働日数+年休日数) AS 年間働日数
FROM 給与データ
WHERE
  年月 BETWEEN
    (
      SELECT FORMAT(DATESERIAL(YEAR(DATEADD("yyyy",-1, DATE())), MONTH(付与基準日),DAY(付与基準日)),"yyyymm")
      FROM 基本情報
      WHERE 基本情報.個人コード = 給与データ.個人コード
    )
    AND
    (
      SELECT FORMAT(DATESERIAL(YEAR(DATE()), MONTH(付与基準日-1),DAY(付与基準日-1)),"yyyymm")
      FROM 基本情報
      WHERE 基本情報.個人コード = 給与データ.個人コード
    )
GROUP BY 給与データ.個人コード;
 
SELECT
  個人コード,
  氏名,
  入社日,
  付与基準日,
  DATEDIFF("m", 入社日, DATESERIAL(YEAR(DATE()),MONTH(付与基準日), DAY(付与基準日))) AS 勤務月数
FROM 基本情報;

投稿日時: 18/10/18 12:07:16
投稿者: たんしお

 Suzu様
 
返信が遅くなり申し訳ありません。
参考のSQLありがとうございます。
また、質問になってしますのですが、年間労働日数の算出にて
データ型が一致しませんとのエラーがでてしまいます。
どこのデータ型が一致していないのかわかりませんでした。
 
最終的には、付与日数を算出したいのですが、
年間労働日数と勤続月数と有給休暇付与基準テーブルを基に
クエリを設定すれば算出可能でしょうか
 
現在の登録情報となります。
給与データ
フィールド データ型
ID   :オートナンバー
年月   :日付/時刻型
個人コード:数値型
氏名   :短いテキスト
所属名  :短いテキスト
労働日数 :数値型
欠勤日数 :数値型
労働時間 :数値型
時間外総計:数値型
土祝出時間:数値型
休出時間 :数値型
深夜残業 :数値型
総労働時間:数値型
年休付与 :数値型
年休日数 :数値型
年休残日数:数値型
 
基本情報
ID    :オートナンバー
個人コード:数値型
氏名     :短いテキスト
入社日  :日付/時刻型
付与基準日:日付/時刻型
 

回答
投稿日時: 18/10/18 16:51:10
投稿者: Suzu

引用:
給与データ
フィールド データ型
ID   :オートナンバー
年月   :日付/時刻型

 
引用:
給与データ(毎月の勤怠データ取込用になります)

 
との事でしたので、
 
ID    年月    個人コード    氏名・・・
1    201710    23342    佐藤太郎
 
としており、年月は、テキスト型を想定していました。
 
 
引用:
最終的には、付与日数を算出したいのですが、
年間労働日数と勤続月数と有給休暇付与基準テーブルを基に
クエリを設定すれば算出可能でしょうか

 
そうですね。
 
先に提示したSQLのクエリを
Q_年間労働日数、Q_勤務月数 としましょうか。
  ※ Q_年間労働日数 のSQL中、年間働日数 → 年間労働日数 と訂正してください。
 
それぞれのクエリのフィールド構成は
 
Q_年間働日数
  個人コード
  年間労働日数
 
Q_勤務月数
  個人コード
  氏名
  入社日
  付与基準日
  勤務月数
 
となりますから、
個人コードで、Q_年間労働日数 と Q_勤務月数 を接続し
 
新クエリ
Q_個人付与条件
  個人コード
  氏名
  年間労働日数
  勤務月数
 
を作ります。その上で 更に新クエリを作ります。
 
SELECT
   Q_個人付与条件.個人コード, Q_個人付与条件.氏名,
   Q_個人付与条件.年間労働日数, Q_個人付与条件.勤務月数,
   MAX(有給休暇付与基準.付与日数) AS 付与数
FROM Q_個人付与条件, 有給休暇付与基準
WHERE
  (
    有給休暇付与基準.所定日数S <= Q_個人付与条件.年間労働日数
    AND
    有給休暇付与基準.所定日数E >= Q_個人付与条件.年間労働日数
  )
  AND
  (
    有給休暇付与基準.勤続月数 <= Q_個人付与条件.勤務月数
  )
GROUP BY
  Q_個人付与条件.個人コード, Q_個人付与条件.氏名,
  Q_個人付与条件.年間労働日数, Q_個人付与条件.勤務月数
;
 
でどうでしょう。

回答
投稿日時: 18/10/18 17:08:42
投稿者: Suzu

あ。。。
 抽出条件に、いつの基準日の人のデータを計算するのかの条件を入れないと
 当年の基準日起算で、給与データ/基本情報 両方にデータがある人のレコードを計算してしまいます。
 
 
多分、計算は 付与基準日 の 月 毎に計算を行う事が多いでしょうから、
仮に、10月 のデータを計算したいなら
 
Q_勤務月数 を
 
SELECT
  個人コード,
  氏名,
  入社日,
  付与基準日,
  DATEDIFF("m", 入社日, DATESERIAL(YEAR(DATE()),MONTH(付与基準日), DAY(付与基準日))) AS 勤務月数
FROM 基本情報
WHERE MONTH(付与基準日)=10;
 
としてください。
 
必要に応じて、抽出条件を、範囲指定や 、パラメータークエリに変更してみてください。
その辺は、ご自分でどうぞ。

投稿日時: 18/10/19 16:03:19
投稿者: たんしお

Suzu様
  
いつもありがとうございます。大変助かります。
年間労働日数の算出にてデータ型が一致しませんとのエラーはなくなったのですが、
フィールド名だけでており、データがでてこないのは、
また何かの該当条件が一致してないということなのでしょうか

トピックに返信