Microsoft Access 掲示板

Microsoft Access 掲示板

条件付きのクエリ作成について

15 コメント
views
6 フォロー

Access初心者です。Accessで資格表の作成をしています。

資格A　1000円
資格B　1000円
資格C　1000円

普通に計算すると3000円なのですが、
資格全て所持している人に対して400円加算される式が作りたいです。
３つ資格持っている人は、3400円という結果になります。

資格、A,B（A,Cも同様）の2つの資格しか持っていない人は適用されないため、2000円です。

このような式はクエリで作成可能でしょうか？
よろしくお願いいたします。

しおこんぶ
作成: 2025/01/17 (金) 15:32:10
通報 ...
1
hatena 2025/01/17 (金) 18:20:29

作成可能と思われます。
回答するには、下記の情報が必要です。

対象のテーブル名
テーブル内のフィールド名、データ型
データ例
2
hatena 2025/01/18 (土) 11:33:51 修正

返答がないのでとりあえず下記のようなテーブル構成だと仮定して回答します。

所持資格テーブル

社員ID資格金額
1A1000
1B1000
1C1000
2A1000
2B1000
3A1000
3C1000

クエリのデザインビューで下記のように設定します。

フィールド: 社員ID 金額計: IIf(Count(資格)>=3,400,0)+Sum(金額)
テーブル: 所持資格テーブル
集計: グループ化 演算

SQLなら下記になります。

SELECT 社員ID, IIF(Count(資格)>=3,400,0) + Sum(金額) AS 金額計
FROM 所持資格テーブル
GROUP BY 社員ID;

出力結果

社員ID金額計
13400
22000
32000
3
しおこんぶ 2025/01/20 (月) 12:58:41 a6738@80964

返信が遅くなり申し訳ありません。
解答ありがとうございます。式はうまく作動しました。

私の説明不足で大変申し訳ないのですが…資格が70件ほどあり、全ての資格ではなく一部の資格でして…
また、所持、未更新という項目があり、所持なら金額表示、未更新なら金額をゼロとしていしています。
一度切の物や、毎年試験する資格があり、落ちると未更新になります。

画像
画像

不慣れなもので、画像を参照して頂ければ嬉しいです。

メインフォームに作業員CDがあり、サブフォームに資格CDの入力をしています。

作業員一人に対して
資格CD1、2、3があればプラス400円
資格CD4，5，6があればプラス400円
・
・
・
（資格CD15まで続きます）

資格CD16以降からはプラス料金はなく、単体の手当金のみになります。
手当金は１千円台から９千円台まであります。
手当金は資格に対する金額であって本人に支給されるのは、所持している人のみの「金額」になります。

いかがでしょうか…？

4
しおこんぶ 2025/01/20 (月) 13:01:32 a6738@80964

すみません。画像貼りなおします。画像1
画像2
5
hatena 2025/01/20 (月) 16:28:31

プラス400円になる資格の組み合わせは下記の5パターンということでしょうか。

資格CD1，2，3
資格CD4，5，6
資格CD7，8，9
資格CD10，11，12
資格CD13，14，15

また、一人の作業員が2パターン以上の資格を持っている場合は、それぞれのパターン毎にプラス400円になるのでしょうか。

例えば、資格CD1，2，3，4，5，6を持っていたらプラス800円になるのでしょうか。

また、結果はクエリとして一覧表示したいのでしょうか。
それとも現状のメインサブフォームで表示出来ればいいのでしょうか。

できればデータ例と希望の結果をマークダウン書式の表として提示してもらえると回答しやすいです。

マークダウン書式については下記に説明がありますので参照してください。

Microsoft Access 掲示板 の使い方 Microsoft Access 掲示板 - zawazawa

上記で説明してありますが、下記のサイトで簡単にマークダウンテーブルが作成できます。

Markdown Tables generator - TablesGenerator.com

テーブルデータのコピーアンドペーストで簡単に作成できます。
希望結果はエクセルで作成してそれをコピーアンドペーストで簡単に作成できます。

6
しおこんぶ 2025/01/21 (火) 10:31:48 a6738@80964

返信ありがとうございます。

Qプラス400円になる資格の組み合わせは下記の5パターンということでしょうか。
資格CD1，2，3
資格CD4，5，6
資格CD7，8，9
資格CD10，11，12
資格CD13，14，15

→はい、5パターンです。

Q一人の作業員が2パターン以上の資格を持っている場合は、それぞれのパターン毎にプラス400円になるのでしょうか。
→はい、パターン毎にプラス400円です。

Q資格CD1，2，3，4，5，6を持っていたらプラス800円になるのでしょうか。
→はい、作用員一人に対して１パターンにつきプラス400円なので、２パターンなら800円、３パターンなら1200円追加されます。

不慣れで見にくい返信で申し訳ございませんでした。
以下の表で少し見やすいでしょうか・・・？

作業員CD資格CD金額
111,200
121,600
131,600
141,200
151,600
161,600
271,200
281,600
291,600
2101,200
2111,600
311,200
321,600
471,200
491,600

結果

作業員CD金額
19,600
27,600
32,800
42,800

Qまた、結果はクエリとして一覧表示したいのでしょうか。
　それとも現状のメインサブフォームで表示出来ればいいのでしょうか。
　→メインサブフォームで表示できればうれしいです。

参照ページの添付までありがとうございます。
これからも精進してまいりますので、よろしくお願いいたします。

7
mayu 2025/01/21 (火) 13:17:18 fc5d2@6c788 >> 6

…資格が70件ほどあり、全ての資格ではなく一部の資格でして…

Qプラス400円になる資格の組み合わせは下記の5パターンということでしょうか。
 →はい、5パターンです。

対象となる資格やパターンの可視化、及び パターンの増減に対応するため
別途、パターンの管理テーブルを作成しましょう

t_資格グループ

資格GRP資格CD
11
12
13
24
25
26
37
38
39
410
411
412
513
514
515

　
■SQL (>> 6に掲載されている「以下の表」の名前を仮に t_資格計算 とします )

SELECT 作業員CD
     , Sum( total ) As 金額
FROM
(
    SELECT 作業員CD
         , Sum( 金額 ) As total
    FROM t_資格計算
    GROUP BY 作業員CD
    
    UNION ALL
    
    SELECT 作業員CD
         , Count(1) * 400
    FROM 
    (
        SELECT x.資格GRP
             , x.作業員CD
        FROM
        (
            SELECT 資格GRP
                 , 資格CD
                 , 作業員CD
            FROM t_資格グループ, 
                 ( SELECT DISTINCT 作業員CD FROM t_資格計算 ) tmp
        ) x
        LEFT JOIN t_資格計算 y
               ON x.作業員CD = y.作業員CD AND
                  x.資格CD = y.資格CD
        GROUP BY x.資格GRP
               , x.作業員CD
        HAVING Count(1) = Count( y.作業員CD )
    ) q_plus
    GROUP BY 作業員CD
) q_union
GROUP BY 作業員CD
ORDER BY 作業員CD
;

　
■結果

作業員CD金額
19,600
27,600
32,800
42,800

　

メインサブフォームで表示できればうれしいです

SQLの結果をクエリとして保存するのではなく
SQL文を直接、フォームのレコードソースに指定するといいでしょう

11
hiroton 2025/01/21 (火) 18:28:44 8ec45@f966d >> 7

何も考えずにレス付けるんですが400ってリテラルもデータベース化できないですかね？

13
mayu 2025/01/21 (火) 19:50:38 修正 fc5d2@6c788 >> 7

400ってリテラルもデータベース化できないですかね？

私が開発するなら、保守性や拡張性を考慮してテーブルへ落とし込みます
　

資格GRP集約名義プラス手当金適用可否
1手当A400TRUE
2手当B400TRUE
3手当C400TRUE
4手当D400TRUE
5手当E400TRUE

　
ただ、マスタ化するかどうかは要件次第ではないでしょうか

  1. 手当金の種類は1つだが、金額が変更される可能性がある
  2. 資格GRP毎に手当金が個別に変更される可能性がある
  3. SQL文に埋め込まれたリテラル値を 他のDBAが根拠や由来を理解できない可能性がある

いずれに該当するのか、これは上流工程で考慮するべき項目ですから
今回のご質問では、この部分のリスクヘッジには言及しませんでした

例えば、リスク1 だけを考慮する場合は
DB化せず、SQL内のリテラルを変数にしてカバーするよう手段を講じるかもしれませんね

+ PARAMETERS prm手当金 CURRENCY;
-          , Count(1) * 400
+          , Count(1) * CCur(Nz(prm手当金, 400))
8
hatena 2025/01/21 (火) 13:56:39

大変分かりやすいデータ例の提示ありがとうございます。
おかげで回答しやすいです。

提示されているテーブル名を「作業員所持資格」と仮定して回答します。

まずはパターン情報を得るための下記のようなテーブルを作成します。

資格パターン

資格CDパターンCD
11
21
31
42
52
62
73
83
93
104
114
124
135
145
155

「作業員所持資格」と「資格パターン」テーブルから下記のようなクロス集計クエリを作成します。

画像1

隠れている「加算手当金:」の式は下記にしてください。

加算手当金: IIf(Count([資格パターン].[パターンCD])=3,400,0)

また「クエリ列見出し」も「1,2,3,4,5」と設定してください。

SQLビューなら下記のコードになります。

TRANSFORM IIf(Count([資格パターン].[パターンCD])=3,400,0) AS 加算手当金
SELECT 作業員所持資格.作業員CD, Sum(作業員所持資格.金額) AS 手当金計
FROM 作業員所持資格 LEFT JOIN 資格パターン ON 作業員所持資格.資格CD = 資格パターン.資格CD
GROUP BY 作業員所持資格.作業員CD
PIVOT 資格パターン.[パターンCD] In (1,2,3,4,5);

上記のクエリの結果は下記になります。

作業員CD手当金計12345
1¥8,800400400000
2¥7,2000040000
3¥2,80000000
4¥2,80000000

これをメインフォームのレコードソースにして、
加算を含む手当金のテキストボックスのコントロールソースに下記の式を設定します。

=[手当金計]+[1]+[2]+[3]+[4]+[5]
9
mayu 2025/01/21 (火) 16:26:46 fc5d2@6c788 >> 8
  • パターンCDの番号は５種類で固定
  • パターンCD毎のデータ件数は３で固定
  • 資格パターン テーブルにおける 資格CD 列の値は重複が発生しない

という要件を全て満たす場合は、以下のSQLでもご希望の結果になりそうですね

SELECT x.作業員CD
     , Sum(x.金額)
           + IIf(sum(IIF(y.パターンCD = 1, 1, 0 )) = 3, 400, 0)
           + IIf(sum(IIF(y.パターンCD = 2, 1, 0 )) = 3, 400, 0)
           + IIf(sum(IIF(y.パターンCD = 3, 1, 0 )) = 3, 400, 0)
           + IIf(sum(IIF(y.パターンCD = 4, 1, 0 )) = 3, 400, 0)
           + IIf(sum(IIF(y.パターンCD = 5, 1, 0 )) = 3, 400, 0) As 金額
FROM 作業員所持資格 x
LEFT JOIN 資格パターン y
       ON x.資格CD = y.資格CD
GROUP BY x.作業員CD
;
10
hatena 2025/01/21 (火) 16:45:46 >> 9

この固定条件ならこちらの方が私のよりシンプルでいいですね。

mayuさんの最初のSQLだと、パターン数やパターン内件数が固定でなくても対応できるのでより対応範囲が広いですね。

14
しおこんぶ 2025/01/22 (水) 09:31:14 a6738@80964 >> 9

たくさんの解答ありがとうございます。
Accessの可能性って無限大ですね。

昨日hatenaさんの式でうまく作動しました。
ただ、リレーションシップで詰まってしまい、返信が遅くなってしましました。
hatenaさんの解答とても分かりやすかったです。ありがとうございます。

mayuさん。私がSQLってだけで苦手意識あったのですが、
mayuさんのSQLは初心者のとても見やすく感動しました。
私がSQLは全く知識ないので、これから参考に勉強させていただきますね。
一緒に考えてくださり本当にありがとうございます。

まだ完結してませんが、お陰様で求めている結果が表示できました。
本当にありがとうございます。

12
hiroton 2025/01/21 (火) 19:01:17 8ec45@f966d

mayuさん提案のように資格パターンの管理ができるようになるとベストだと思いますが、考え方としては>> 2のhatenaさんの内容でも十分行けると思います
肝となるのはCount(資格)>=3部分で、集計結果から資格パターンにマッチするような集計を行えばいいので、たとえば、「資格1,2,3をすべて所持している」ならSum(IIf([資格CD] In (1,2,3),1,0)=3で表現できます

5パターンすべてを合算すると、

総金額: Sum([金額])+IIf(Sum(IIf([資格cd] In (1,2,3),1,0))=3,400,0)+IIf(Sum(IIf([資格cd] In (4,5,6),1,0))=3,400,0)+IIf(Sum(IIf([資格cd] In (7,8,9),1,0))=3,400,0)+IIf(Sum(IIf([資格cd] In (10,11,12),1,0))=3,400,0)+IIf(Sum(IIf([資格cd] In (13,14,15),1,0))=3,400,0)

ですね

1行につなげて見にくくなるようなら各資格ごとのグループでフィールドを作成して最後に合計する形でもいいと思います

基本金額: Sum([金額])
資格G1: IIf(Sum(IIf([資格cd] In (1,2,3),1,0))=3,400,0)
資格G2: IIf(Sum(IIf([資格cd] In (4,5,6),1,0))=3,400,0)
資格G3: IIf(Sum(IIf([資格cd] In (7,8,9),1,0))=3,400,0)
資格G4: IIf(Sum(IIf([資格cd] In (10,11,12),1,0))=3,400,0)
資格G5: IIf(Sum(IIf([資格cd] In (13,14,15),1,0))=3,400,0)

総金額: 基本金額+資格G1+資格G2+資格G3+資格G4+資格G5
15
しおこんぶ 2025/01/22 (水) 09:31:53 a6738@80964 >> 12

hirotonさん
イフ関数での簡易的な式ありがとうございます。
先日に続き解答してくださり、感謝の気持ちでいっぱいです。
こちらも参考にさせていただきますね。ありがとうございます。