- ベストアンサー
Accessデータベースのクエリによる集計方法のアドバイスをお願いします
- Accessデータベースにおいて、特定の条件によって値を加算・減算し集計する方法についてアドバイスをお願いします。
- テーブル内の区分1と区分2の金額を減算し、除外金額columnと売上columnを求めたいです。
- クエリまたはSQLを使用して、理想的な集計結果を得る方法や妥協案についてアドバイスをお願いします。
- sujino
- お礼率86% (65/75)
- その他(データベース)
- 回答数5
- ありがとう数5
- みんなの回答 （5）
- 専門家の回答
質問者が選んだベストアンサー
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;
その他の回答 (4)
- chie65536（@chie65535）
- ベストアンサー率44% (8812/19983)
続き。 ここまで出来れば「最大、最小を用いる」ことで「理想通りのクエリ」が作れます。 そうして作ったクエリが 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様とさせて頂きます。 また、機会がありましたら、アドバイスお願いいたします。
- m3_maki
- ベストアンサー率64% (296/460)
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様とさせて頂きます。 また、機会がありましたら、アドバイスお願いいたしまうす。
- m3_maki
- ベストアンサー率64% (296/460)
- chie65536（@chie65535）
- ベストアンサー率44% (8812/19983)
＞▲妥協案として ＞区分２の金額をマイナスにする。 惜しい所まで行っています。 「区分２の金額をマイナスにする」のは「クエリで可能」です。 「区分２の金額をマイナスにする」のがクエリで出来れば、あとは「受付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にはコース（区分１）や、付帯料金（区分２）が複数あります。 そのため、特定の１行を抽出すると、集計結果が合わなくなります。 例のテーブルを、省略して提示してしまったので、紛らわしくなり申し訳ありませんでした。
お礼
f_a_007様 アドバイスありがとうございます。 まだ、テストしていませんが、まさしく私がほしかった理想の形です。 IFF文で、区分を分別して、ASでカラムを分ける。 多分、この形は今後頻繁につかうと思いますので、勉強になりました。 ありがとうございます。