みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

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

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

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

投稿日時 - 2015-07-03 15:41:57

QNo.9005316

困ってます

質問者が選んだベストアンサー

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;

投稿日時 - 2015-07-03 16:14:08

お礼

f_a_007様

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

投稿日時 - 2015-07-03 21:30:32

ANo.2

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

続き。

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

そうして作ったクエリが

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

となります。

投稿日時 - 2015-07-03 16:22:31

お礼

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

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

投稿日時 - 2015-07-03 21:37:07

ANo.4

No.3 です。

訂正です。

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

投稿日時 - 2015-07-03 16:22:29

お礼

m3_maki様

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

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

投稿日時 - 2015-07-03 21:35:19

ANo.3

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

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

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

投稿日時 - 2015-07-03 16:15:30

ANo.1

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

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

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

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

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

そのようにして作ったクエリが、以下のクエリです(元テーブルのテーブル名は「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

投稿日時 - 2015-07-03 16:11:24

お礼

chie65535様

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

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

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

その為、1つの受付CDにはコース(区分1)や、付帯料金(区分2)が複数あります。

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

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

投稿日時 - 2015-07-03 21:27:35

あなたにオススメの質問