Access (一般機能)

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

 
(Windows 7 Professional : Access 2016)
集計クエリ
投稿日時: 19/07/11 14:18:55
投稿者: kazz

お世話になります。
下記のテーブルがあります(主キーは科目と内訳)
レコードの2行目、4行目、5行目は科目と内訳は異なりますが、
銀行番号、支店番号、預金種目、口座番号は同じです。
この4つのフィールドをキーに金額を合計し、
且つ、科目と内訳を昇順に表示させるクエリまたはSQLが
ありましたら教えていただけませんでしょうか。
 
4行目と5行目のレコードが2行目に加算されるようなイメージです
 
よろしくお願いいたします
 
科目 内訳 銀行番号 支店番号 預金種目 口座番号 金額
402  0001     1    100     1    1234567 10,000
402  0002     2    200     1    7654321 20,000
402  0003     5     50     2    1111111 30,000
403  0001      2    200     1    7654321 50,000
403  0010     2    200     1    7654321 15,000
 
結果
科目 内訳 銀行番号 支店番号 預金種目 口座番号 金額
402 0001     1    100     1    1234567 10,000
402 0002     2    200     1    7654321 85,000←
402 0003     5    50     2    1111111 30,000

回答
投稿日時: 19/07/11 16:17:03
投稿者: sk

引用:
主キーは科目と内訳

引用:
銀行番号、支店番号、預金種目、口座番号

引用:
この4つのフィールドをキーに金額を合計し、
且つ、科目と内訳を昇順に表示させるクエリまたはSQL

( SQL ビュー)
----------------------------------------------------------------------
SELECT [テーブル名].[科目], 
       [テーブル名].[内訳], 
       [テーブル名].[銀行番号], 
       [テーブル名].[支店番号], 
       [テーブル名].[預金種目], 
       [テーブル名].[口座番号] 
       (SELECT Sum(tmp.[金額]) 
        FROM [テーブル名] tmp 
        WHERE tmp.[銀行番号] = [テーブル名].[銀行番号] 
          AND tmp.[支店番号] = [テーブル名].[支店番号] 
          AND tmp.[預金種目] = [テーブル名].[預金種目] 
          AND tmp.[口座番号] = [テーブル名].[口座番号]) AS [金額] 
FROM [テーブル名] 
WHERE NOT EXISTS (SELECT tmp.* 
                  FROM [テーブル名] tmp 
                  WHERE tmp.[銀行番号] = [テーブル名].[銀行番号] 
                    AND tmp.[支店番号] = [テーブル名].[支店番号] 
                    AND tmp.[預金種目] = [テーブル名].[預金種目] 
                    AND tmp.[口座番号] = [テーブル名].[口座番号] 
                    AND (   (tmp.[科目] < [テーブル名].[科目])
                         OR (    tmp.[科目] = [テーブル名].[科目]
                             AND tmp.[内訳] < [テーブル名].[内訳])))
ORDER BY [テーブル名].[科目], 
         [テーブル名].[内訳];
----------------------------------------------------------------------
(テーブル名は適宜修正すること)
 
以上のような感じでしょうか。

回答
投稿日時: 19/07/11 21:39:33
投稿者: mayu.

引用:
この4つのフィールドをキーに金額を合計し、
且つ、科目と内訳を昇順に表示させるクエリまたはSQL

以下のSQLでもご希望の結果になるでしょう。
 
SELECT CLng( 
           Left$( Min( Format$( x.科目, String$( 10, '0' ) ) & x.内訳 )
                , 10
           ) 
       ) As 科目
     , Mid$( 
             Min( Format$( x.科目, String$( 10, '0' ) ) & x.内訳 )
           , 11
       ) As 内訳
     , 銀行番号
     , 支店番号
     , 預金種目
     , 口座番号
     , Sum( x.金額 ) As 金額
FROM テーブル名 x
GROUP BY 銀行番号
       , 支店番号
       , 預金種目
       , 口座番号
ORDER BY 1, 2 ;

回答
投稿日時: 19/07/12 20:10:06
投稿者: jung

こんなのも
 
SELECT
       First(科目)
     , First(内訳)
     , 銀行番号
     , 支店番号
     , 預金種目
     , 口座番号
     , Sum(金額)
FROM テーブル名
GROUP BY 銀行番号
       , 支店番号
       , 預金種目
       , 口座番号
ORDER BY 科目, 内訳,銀行番号, 支店番号, 預金種目, 口座番号;

投稿日時: 19/07/18 13:39:21
投稿者: kazz

skさん、mayu.さん、jungさん、ありがとうございます
後出しになってしまいましたが、他にフィールド「振込日」があり、
振込日且つ、金額が0より大きいレコードを抽出したいのですが
下記のSQLであってますでしょうか?skさん、mayu.さん、jungさんから
教えていただいたSQLに加筆しました
(テーブル名 T_1)
 
skさん方式
SELECT T_1.科目, T_1.内訳, T_1.銀行番号, T_1.支店番号, T_1.預金種目, T_1.口座番号, (SELECT Sum(tmp.[金額]) FROM [T_1] tmp WHERE (振込日=#2019/6/28#)
          AND (tmp.[金額] >0)
          AND (tmp.[銀行番号] = [T_1].[銀行番号]
          AND tmp.[支店番号] = [T_1].[支店番号]
          AND tmp.[預金種目] = [T_1].[預金種目]
          AND tmp.[口座番号] = [T_1].[口座番号])) AS 金額
FROM T_1
WHERE ((((SELECT Sum(tmp.[金額]) FROM [T_1] tmp WHERE (振込日=#2019/6/28#)
          AND (tmp.[金額] >0)
          AND (tmp.[銀行番号] = [T_1].[銀行番号]
          AND tmp.[支店番号] = [T_1].[支店番号]
          AND tmp.[預金種目] = [T_1].[預金種目]
          AND tmp.[口座番号] = [T_1].[口座番号]))) Is Not Null) AND ((Exists (SELECT tmp.* FROM [T_1] tmp
 WHERE tmp.[銀行番号] = [T_1].[銀行番号]
 AND tmp.[支店番号] = [T_1].[支店番号]
 AND tmp.[預金種目] = [T_1].[預金種目]
 AND tmp.[口座番号] = [T_1].[口座番号]
 AND ( (tmp.[科目] < [T_1].[科目])
 OR ( tmp.[科目] = [T_1].[科目]
 AND tmp.[内訳] < [T_1].[内訳]))))=False))
ORDER BY T_1.科目, T_1.内訳;
 
 
mayu.さん方式
SELECT CLng(Left$(Min(Format$(x.科目,String$(10,'0')) & x.内訳),10)) AS 科目, CLng(Mid$(Min(Format$([x].[科目],String$(10,'0')) & [x].[内訳]),11)) AS 内訳, x.銀行番号, x.支店番号, x.預金種目, x.口座番号, Sum(x.金額) AS 金額
FROM T_1 AS x
WHERE (((x.振込日)=#6/28/2019#))
GROUP BY x.銀行番号, x.支店番号, x.預金種目, x.口座番号
HAVING (((Sum(x.金額))>0))
ORDER BY 1, 2;
 
jungさん方式
(FirstをMinに変えました)
SELECT Min(T_1.科目) AS 科目の先頭, Min(T_1.内訳) AS 内訳の先頭, T_1.銀行番号, T_1.支店番号, T_1.預金種目, T_1.口座番号, Sum(T_1.金額) AS 金額の合計
FROM T_1
WHERE (((T_1.振込日)=#6/28/2019#))
GROUP BY T_1.銀行番号, T_1.支店番号, T_1.預金種目, T_1.口座番号
HAVING (((Sum(T_1.金額))>0))
ORDER BY Min(T_1.科目), Min(T_1.内訳);

回答
投稿日時: 19/07/18 14:01:32
投稿者: sk

引用:
後出しになってしまいましたが、他にフィールド「振込日」があり、
振込日且つ金額が0より大きいレコードを抽出したいのですが

後者はともかく、前者の条件が不明瞭です。
(「[振込日]の値が 0 より大きく、かつ[金額]の値が 0 より大きい」
とも解釈できる)
 
引用:
振込日=#2019/6/28#

仮に上記の通りなら「フィールド[振込日]の値が 2019/06/28 である」
ということになりますが。
 
また、最初のご質問における下記の抽出条件や集計範囲と
どのように組み合わせて、どのような結果を得たいのかを
具体的に明記されることをお奨めします。
 
引用:
下記のテーブルがあります(主キーは科目と内訳)
レコードの2行目、4行目、5行目は科目と内訳は異なりますが、
銀行番号、支店番号、預金種目、口座番号は同じです。
この4つのフィールドをキーに金額を合計し、
且つ、科目と内訳を昇順に表示させるクエリまたはSQLが
ありましたら教えていただけませんでしょうか。

回答
投稿日時: 19/07/18 16:54:16
投稿者: よろずや

>(FirstをMinに変えました)
 
402  0002 のレコードと
403  0001 のレコードを合計したとき、
402 0001 になりますけど、それはよろしいんですか?
 
 
 
 
※ First は、うかつに使うと予期せぬ結果をもたらします。
 
SELECT First(Q_1.科目) AS 科目の先頭, First(Q_1.内訳) AS 内訳の先頭, Q_1.銀行番号, Q_1.支店番号, Q_1.預金種目, Q_1.口座番号, Sum(Q_1.金額) AS 金額の合計
FROM (
SELECT * FROM T_1 WHERE 振込日 = #6/28/2019#
ORDER BY 銀行番号, 支店番号, 預金種目, 口座番号, 科目, 内訳
) Q_1
GROUP BY Q_1.銀行番号, Q_1.支店番号, Q_1.預金種目, Q_1.口座番号
HAVING (((Sum(Q_1.金額))>0))
ORDER BY 科目の先頭, 内訳の先頭;

投稿日時: 19/07/18 17:02:58
投稿者: kazz

skさん、
説明不足でした。また勘違いしておりまして
主キーは科目、内訳ではなくフィールド名 AutoNum(オートナンバー型)でした
申し訳ありませんでした
 
私が希望するクエリまたはSQLは、
 
(1)振込日が2019/6/28のレコードで且つ金額が0より大きいレコードを抽出対象とし
 
(2)銀行番号、支店番号、預金種目、口座番号、これら4つのフィールドが
全て同一のレコードが存在した場合は金額を合算し、科目と内訳が最小値の
レコードに金額の合算を表示し、
(4行目と5行目のレコードが2行目に加算されるようなイメージ)
 
(3)科目→内訳の優先順位で、それぞれ昇順で表示
 
このようなクエリまたはSQLを希望しております
この説明で伝わりますでしょうか
 
※1 フィールド名、AutoNumは割愛しています
※2 実際にはテーブル内に振込日が2019/6/28以外のレコードと金額が0であるレコードが存在しています
 
振込日    科目    内訳    銀行番号    支店番号    預金種目    口座番号    金額
2019/06/28    402    0001    1    100    1    1234567    10,000
2019/06/28    402    0002    2    200    1    7654321    20,000
2019/06/28    402    0003    5    50    2    1111111    30,000
2019/06/28    403    0001    2    200    1    7654321    50,000
2019/06/28    403    0010    2    200    1    7654321    15,000
                            
振込日    科目    内訳    銀行番号    支店番号    預金種目    口座番号    金額
2019/06/28    402    0001    1    100    1    1234567    10,000
2019/06/28    402    0002    2    200    1    7654321    85,000
2019/06/28    402    0003    5    50    2    1111111    30,000

回答
投稿日時: 19/07/18 18:00:44
投稿者: sk

kazz さんの引用:
(1)振込日が2019/6/28のレコードで且つ金額が0より大きいレコードを抽出対象とし
  
(2)銀行番号、支店番号、預金種目、口座番号、これら4つのフィールドが
全て同一のレコードが存在した場合は金額を合算し、科目と内訳が最小値
レコードに金額の合算を表示し、
(4行目と5行目のレコードが2行目に加算されるようなイメージ
  
(3)科目→内訳の優先順位で、それぞれ昇順で表示

よろずや さんが指摘された件については
どのようにお考えなのでしょうか。
 
kazz さんの引用:
FirstをMinに変えました

よろずや さんの引用:
402  0002 のレコードと
403  0001 のレコードを合計したとき、
402 0001 になりますけど、それはよろしいんですか?

「[銀行番号],[支店番号],[預金種目],[口座番号]の
それぞれの値が同じグループであるレコードを
[科目]の昇順、[内訳]の昇順に並べ替えた時に
(各グループにおいて)先頭になるレコードを取得する」のと、
「[銀行番号],[支店番号],[預金種目],[口座番号]の
それぞれの値が同じグループにおける
[科目]の最小値と[内訳]の最小値を別々に求める」のとでは
明らかに処理内容が異なります。

回答
投稿日時: 19/07/18 22:38:06
投稿者: mayu.

3点、コメントしておきます。
  
■ 1点目
  

引用:
SELECT Min(T_1.科目) AS 科目の先頭, Min(T_1.内訳) AS 内訳の先頭

集約関数の性質
及び
複数フィールドをキーにして並び替えた場合に先頭となるレコードの特定方法
を理解していないと
こういうミスを犯しがちなので、私が提示した SQL では
並び替えの第1キーとなる 科目 フィールドに対しては、
  
    Min( x.科目 )
  
とせず、敢えて
  
    CLng( Left$( Min( Format$( x.科目, String$( 10, '0' ) ) & x.内訳 ), 10 ) )
  
と記述して
第2キーとなる 内訳 フィールドの特定方法とロジックを合わせたのですけど
( 固定長の結合値にしてから、最小値を求め、最後に各キーの位置を割り出して切り出す )
意図が伝わっていなかったようですから、解説を省いたのが裏目に出ましたね。
  
■ 2点目
 
引用:
(1)振込日が2019/6/28のレコードで且つ金額が0より大きいレコードを抽出対象とし

  グループ単位で全レコードを集計した結果、
  合計金額が 0 超過のグループのみを表示したい ( ※1 )

  あらかじめ 金額が 0 以下のレコードは除外しておき、
  金額が 0 超過のレコードのみを対象として集計を実施したい ( ※2 )

SQLの記述方法・結果ともに異なります。
  
文脈からだと、WHERE句に条件を指定する( ※2 )をご希望のようですが、
ご自身が記述した 2019/07/18 13:39:21 の SQL では
HAVING句に条件が記述されているため、( ※1 )のロジックになっています。
  
デザインビューに依存してクエリを作ると
操作手順によっては、意図しない結果になる場合がありますから
必ずご自身で SQL と結果セット 両方をチェックなさったほうがいいでしょう。
  
■ 3点目
  
First関数、Last関数は
引数となるフィールドの値が1種類のみという条件下であれば
使用してもいいでしょう。
  
値が1種類のフィールドを、SELECT句への射影 という目的のためだけに
GROUP BY句に含めるのは無駄ですし、
グループ化の対象フィールドにインデックスが設定されているなどの理由で
GROUP BY句には 含めたくない場合もあるでしょうから。
  
今回のケースだと、振込日 フィールドが該当していますよね。
したがって、ご希望の SQL は ↓ ではないでしょうか。
 
SELECT First( x.振込日 ) As 振込日
     , Min( x.科目 ) As 科目
     , Mid$( 
             Min( Format$( x.科目, String$( 10, '0' ) ) & x.内訳 )
           , 11
       ) As 内訳
     , 銀行番号
     , 支店番号
     , 預金種目
     , 口座番号
     , Sum( x.金額 ) As 金額
FROM T_1  x
WHERE x.振込日 = #2019/6/28#
  AND x.金額 > 0
GROUP BY 銀行番号
       , 支店番号
       , 預金種目
       , 口座番号
ORDER BY 2, 3 ;

トピックに返信