Access 条件によって、加算・減算

質問

フィールドの値によって加算・減算し集計したいのですが、アドバイスを頂けると幸いです。

◎環境
Access2010

◎テーブル
ID（AI）｜受付CD（テキスト）｜科目CD（テキスト）｜区分（数値）｜金額（数値）
1 　　　｜2015062222　　　　｜0001　　　　　　　｜1　　　　　 ｜100000
2 　　　｜2015062222　　　　｜0005　　　　　　　｜2　　　　　 ｜30000
3 　　　｜2015063333　　　　｜0001　　　　　　　｜1　　　　　 ｜120000
4 　　　｜2015063333　　　　｜0007　　　　　　　｜2　　　　　 ｜70000

上記テーブルのデータで、区分１から区分２の金額を減算出来ればと考えております。

その場合、どういったクエリを作成すればよいかわかりません。

◎理想の集計結果
受付CD（テキスト）｜金額（数値）｜除外金額（数値）｜売上（数値）
2015062222　　　　｜100000　　 ｜30000　　　　　 ｜70000
2015063333　　　　｜120000　　 ｜70000　　　　　 ｜50000
区分２の項目を別の列にできれば、一番理想です。

▲妥協案として
ID（AI）｜受付CD（テキスト）｜科目CD（テキスト）｜区分（数値）｜金額（数値）
1 　　　｜2015062222　　　　｜0001　　　　　　　｜1　　　　　 ｜100000
2 　　　｜2015062222　　　　｜0005　　　　　　　｜2　　　　　 ｜-30000
3 　　　｜2015063333　　　　｜0001　　　　　　　｜1　　　　　 ｜120000
4 　　　｜2015063333　　　　｜0007　　　　　　　｜2　　　　　 ｜-70000
区分２の金額をマイナスにする。

選択クエリ・SQLどちらでも、実装できれば結構です。

アドバイス頂けましたら幸いです。

回答No.2

山田 太郎（@f_a_007） · ベストアンサー

SELET AAA, SUM(BBB) AS CCC FROM DDD GROUP BY AAA　構文のアレンジで・・・。

SELECT
　　受付CD,
　　Sum(IIf([区分1]=1,金額,0)) AS 合計1,
　　Sum(IIf([区分1]=2,.金額,0)) AS 合計2,
　　[合計1]-[合計2] AS 売上
FROM AAAA
GROUP BY 受付CD
ORDER BY 受付CD;

お礼

f_a_007様

アドバイスありがとうございます。
まだ、テストしていませんが、まさしく私がほしかった理想の形です。
IFF文で、区分を分別して、ASでカラムを分ける。
多分、この形は今後頻繁につかうと思いますので、勉強になりました。
ありがとうございます。

回答No.5

chie65535

続き。

ここまで出来れば「最大、最小を用いる」ことで「理想通りのクエリ」が作れます。

そうして作ったクエリが

SELECT TABLE1.受付CD, Max(IIf([区分]<>2,[TABLE1].[金額],0)) AS 金額, Max(IIf([区分]<>2,0,[TABLE1].[金額])) AS 除外金額, Sum(IIf([区分]<>2,[TABLE1].[金額],-[TABLE1].[金額])) AS 売上
FROM TABLE1
GROUP BY TABLE1.受付CD;

です。

結果は

受付CD（テキスト）｜金額（数値）｜除外金額（数値）｜売上（数値）
2015062222　　　　｜100000　　 ｜30000　　　　　 ｜70000
2015063333　　　　｜120000　　 ｜70000　　　　　 ｜50000

となります。

お礼

chie65535様
重ね重ね、アドバイス頂きありがとうございます。

今回のBAは、一番早く私が理想の形の書き方をお教え頂いた、#2のf_a_007様とさせて頂きます。
また、機会がありましたら、アドバイスお願いいたします。

回答No.4

m3_maki

No.3 です。

訂正です。

SELECT テーブル.受付CD,
Sum(IIf([区分]=1,[テーブル].[金額],0)) AS 金額,
Sum(IIf([区分]=1,0,[テーブル].[金額])) AS 除外金額,
Sum(IIf([区分]=1,[テーブル].[金額],[テーブル].[金額]*(-1))) AS 売上
FROM テーブル
GROUP BY テーブル.受付CD;

お礼

m3_maki様

アドバイス頂きありがとうございます。
書き方の注意事項も教えて頂き、勉強になりました。

ご親切にお教え頂き、大変申し訳ありませんが、今回のBAは一番早く適格に回答頂いた、#2のf_a_007様とさせて頂きます。
また、機会がありましたら、アドバイスお願いいたしまうす。

回答No.3

m3_maki

◎理想の集計結果 の方で（笑

SELECT テーブル.受付CD,
Sum(IIf([区分]=1,[テーブル].[金額],0)) AS 金額,
Sum(IIf([区分]=1,0,[テーブル].[金額])) AS 除外金額,
Sum(テーブル.金額) AS 売上
FROM テーブル
GROUP BY テーブル.受付CD;

元のテーブルのフィールド名「金額」と
集計されたフィールド「金額」を区別する必要があるため
[テーブル].[金額] のように、テーブル名が省略されない形で記述する必要があります。

回答No.1

chie65535

＞▲妥協案として
＞区分２の金額をマイナスにする。

惜しい所まで行っています。

「区分２の金額をマイナスにする」のは「クエリで可能」です。

「区分２の金額をマイナスにする」のがクエリで出来れば、あとは「受付CD単位で集計するだけ」です。

集計にID、科目CD、区分は使えないので、これら３つは、とりあえず「最小値」を表示する事にします。

そのようにして作ったクエリが、以下のクエリです（元テーブルのテーブル名は「TABLE1」とします）

SELECT Min(TABLE1.ID) AS ID, TABLE1.受付CD, Min(TABLE1.科目CD) AS 科目CD, Min(TABLE1.区分) AS 区分, Sum(IIf([TABLE1].[区分]=2,-[TABLE1].[金額],[TABLE1].[金額])) AS 金額
FROM TABLE1
GROUP BY TABLE1.受付CD;

結果は以下のようになります。

ID（AI）｜受付CD（テキスト）｜科目CD（テキスト）｜区分（数値）｜金額（数値）
1 　　　｜2015062222　　　　｜0001　　　　　　　｜1　　　　　 ｜70000
3 　　　｜2015063333　　　　｜0001　　　　　　　｜1　　　　　 ｜50000

お礼

chie65535様

アドバイスありがとうございます。

すいません、私の例が紛らわしいからだと思うのですが、minを使うと言う事はの科目CD、区分の最少行しか集計出来ないのではと思います。
実際のレコードには、科目CDが複数あり、特定の科目CDの区分は一意です。

具体的には、売上には、室料とその追加オプションメニューが含まれておりまして、純粋な室料を算出したいと考えております。
が、区分１は全体の金額、科目２はオプションの金額、区分１の中でもコース・時間帯によって金額が変わります。
その為、区分１の科目○○（全体のコース料金）から、コースの中で使用する材料や機器の使用料（区分２）を減算し、純粋な室料を集計したかったのです。

その為、１つの受付CDにはコース（区分１）や、付帯料金（区分２）が複数あります。

そのため、特定の１行を抽出すると、集計結果が合わなくなります。

例のテーブルを、省略して提示してしまったので、紛らわしくなり申し訳ありませんでした。