Microsoft Access 掲示板

同じ内容のフィールド列に別々の結果を表示したい&数値がゼロ0の列は非表示にしたい

11 コメント

views

4 フォロー

前回の回答、2020/01/25 (土) 09:05:50 に記述した SQL へ
金額が 0 ではない科目が表示対象になるよう抽出条件を加えれば
ご希望の結果になるでしょう。
　

SELECT Max( IIf( q.勘定科目コード < 5000, q.勘定科目コード ) )  As ex1
     , Max( IIf( q.勘定科目コード < 5000, q.勘定科目 ) )        As ex2
     , Max( IIf( q.勘定科目コード < 5000, q.cost ) )            As 金額_費用
     , Max( IIf( q.勘定科目コード > 4999, q.勘定科目コード ) )  As 勘定科目コード
     , Max( IIf( q.勘定科目コード > 4999, q.勘定科目 ) )        As 勘定科目
     , Max( IIf( q.勘定科目コード > 4999, q.revenue ) )         As 金額_収益
FROM
(
    SELECT x.勘定科目コード
         , x.勘定科目
         , x.[金額(費用)] As cost
         , x.[金額(収益)] As revenue
         , Count(1)      As gnum
    FROM 損益計算書 x
       , 損益計算書 y
    WHERE x.勘定科目コード Between 4000 And 5999
      AND y.勘定科目コード Between 4000 And 5999
      AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
      AND x.勘定科目コード >= y.勘定科目コード
      AND Abs( x.[金額(費用)] ) > 0
      AND Abs( y.[金額(費用)] ) > 0
    GROUP BY x.勘定科目コード
           , x.勘定科目
           , x.[金額(費用)]
           , x.[金額(収益)]
) q
GROUP BY gnum
ORDER BY gnum ;

mayu様
前回に引き続きありあとうございます。

前回はmayu様の教えて頂いた集約関数のやり方で解決させて頂きましたので、
こちらの方はをあまり見ずに申し訳なく思っています。

上記について教えて頂きたく。
q=テーブルorクエリ名
x.yは何のテーブルorクエリに該当するのでしょうか？(自己結合というもの？)

それにちなんでfrom句の損益計算書 x,損益計算書 yは損益計算書　As x,損益計算書 As yを単純に省略してるだけ？
もしくは最初のx yと関係してる？

お手数ですが宜しくお願い致します。

x.yは何のテーブルorクエリに該当するのでしょうか？(自己結合というもの？)

x と y は両方ともに損益計算書のことですが
既にご理解いただいてるように、自己結合を駆使した場合は
同じ名前のオブジェクトを複数使用することになりますから
各々を区別するため、ユニークな名前を付与する必要があります。

from句の損益計算書 x,損益計算書 yは
損益計算書　As x,損益計算書 As yを単純に省略してるだけ？

はい。その通りです。
Microsoft Access では、エイリアスの利用において
FROM句の As は省略可能で SELECT句の As は省略不可
という仕様になっています。

q=テーブルorクエリ名

テーブル名でもクエリ名でもないのですが、意味合いとしてはクエリになります。

SELECT x.勘定科目コード ...(略)... , x.[金額(収益)]

この部分で、一つの完結したクエリ( SQL文 )になり
Accessにおいても、名前を付けて保存することが可能です。
もちろん、保存せずに使用することも可能で
その場合は、インラインビューやサブクエリという言い方をします。

今回の場合だと、インラインビューになっていて
q というのは、インラインビューに付けた名前です。

このビューの役割は
4000番台のコードと 5000番台のコードそれぞれにグループ連番を付与することですが
累計計算も同時に出来たりしますから
以下にデモンストレーションを掲載しておきます。

( 続き )

■ 損益計算書

勘定科目コード	勘定科目	金額(費用)	金額(収益)
4001	仕入	25000	-25000
4002	売上原価	0	0
4003	発送費	0	0
4004	減価償却費	500000	-500000
4005	広告宣伝費	120000	-120000
4006	通信費	7000	-7000
4007	水道光熱費	10000	-10000
4008	租税公課	0	0
4009	給料	0	0
4010	旅費交通費	-14000	14000
4011	支払保険料	0	0
4012	消耗品費	4000	-4000
4013	雑損	0	0
4014	貸倒引当金繰入	0	0
5001	売上	-3500	3500
5002	商品売買益	0	0
5003	受取手数料	0	0
5004	受取家賃	70	-70
5005	受取利息	0	0
5006	受取配当金	0	0
5007	雑益	0	0
5008	有価証券利息	-100	100
5009	有価証券売却益	0	0
5010	固定資産売却益	0	0
5011	償却債権取立益	-120	120

　
■ SQL

SELECT x.勘定科目コード
     , x.勘定科目
     , x.[金額(費用)] As cost
     , x.[金額(収益)] As revenue
     , Count(1)      As gnum
     , Sum( IIf( x.勘定科目コード \ 1000 = 4, y.[金額(費用)], 0 ) ) As total_cost
     , Sum( IIf( x.勘定科目コード \ 1000 = 5, y.[金額(収益)], 0 ) ) As total_reve
FROM 損益計算書 x
   , 損益計算書 y
WHERE x.勘定科目コード Between 4000 And 5999
  AND y.勘定科目コード Between 4000 And 5999
  AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
  AND x.勘定科目コード >= y.勘定科目コード
  AND Abs( x.[金額(費用)] ) > 0
  AND Abs( y.[金額(費用)] ) > 0
GROUP BY x.勘定科目コード
       , x.勘定科目
       , x.[金額(費用)]
       , x.[金額(収益)]
ORDER BY x.勘定科目コード ;

　
■ 結果

勘定科目コード	勘定科目	cost	revenue	gnum	total_cost	total_reve
4001	仕入	25000	-25000	1	25000	0
4004	減価償却費	500000	-500000	2	525000	0
4005	広告宣伝費	120000	-120000	3	645000	0
4006	通信費	7000	-7000	4	652000	0
4007	水道光熱費	10000	-10000	5	662000	0
4010	旅費交通費	-14000	14000	6	648000	0
4012	消耗品費	4000	-4000	7	652000	0
5001	売上	-3500	3500	1	0	3500
5004	受取家賃	70	-70	2	0	3430
5008	有価証券利息	-100	100	3	0	3530
5011	償却債権取立益	-120	120	4	0	3650

回答ありがとうございます。
教えて頂いた通りにできました。
また、教えて頂いて恐縮ですが下記の様にするにはどうすればいよいか教えて頂きたく。
・1000番代と9002,9004,9005番代が左側、2000番代と3000番代が右側になるようにする。

この結果を出すためのSQLコマンドがうまくいきません
↓書いたＳＱＬコマンド
※テーブル名、損益計算書→貸借対照表
SELECT Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].[勘定科目コード])) AS 勘定科目コード
, Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].[勘定科目])) AS 勘定科目
, Max(IIf([貸借対照表].[勘定科目コード]<2000 Or [貸借対照表].[勘定科目コード]>9000,[貸借対照表].cost)) AS 金額_資産
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].[勘定科目コード])) AS 勘定科目コード2
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].[勘定科目])) AS 勘定科目2
, Max(IIf([貸借対照表].[勘定科目コード] Between 2000 And 3001,[貸借対照表].revenue)) AS 金額_負債, 貸借対照表.gnum
FROM (SELECT x.勘定科目コード
, x.勘定科目, x.[金額(資産)] AS cost
, x.[金額(負債)] AS revenue
, Count(1) AS gnum
FROM 貸借対照表 AS x
, 貸借対照表 AS y
WHERE x.勘定科目コード Between 1001 And 3001 Or x.勘定科目コード=9002 Or x.勘定科目コード=9004 Or x.勘定科目コード=9005
AND  y.勘定科目コード Between 1001 And 3001 Or y.勘定科目コード=9002 Or y.勘定科目コード=9004 Or y.勘定科目コード=9005
AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000
AND x.勘定科目コード >= y.勘定科目コード
AND Abs( x.[金額(資産)] ) > 0
AND Abs( y.[金額(資産)] ) > 0
GROUP BY x.勘定科目コード
, x.勘定科目
, x.[金額(資産)]
, x.[金額(負債)])  AS 貸借対照表
GROUP BY 貸借対照表.gnum
ORDER BY 貸借対照表.gnum;

返した結果が下記になります
勘定科目コード　勘定科目　　　金額_資産勘定科目コード2 勘定科目2　金額_負債 gnum
9005 　　　減価償却累計額　0 　　　　　　　　　37
9004 　　　立替金　　　70000 　　3001 預り金　　50000 73

gnumの列を見ると並びがきれいに並ばなくなります。

何卒教えて頂きたくお願い致します。

・1000番代と9002,9004,9005番代が左側、2000番代と3000番代が右側になるようにする。
この結果を出すためのSQLコマンドがうまくいきません

gnumの列を見ると並びがきれいに並ばなくなります。

左側に表示するコードと、右側に表示するコードに規則性や共通点が無いですから

  AND x.勘定科目コード \ 1000 = y.勘定科目コード \ 1000

という区分けが、意味をなさなくなったのが原因です。

とはいえ、
SQL文の記述が、読みにくくて見通しが悪い場合は
条件の分岐や、マジックナンバーの追記といった手法を採用すると
記述がますます汚くなりますから
設計を見直したり工夫するのが、定番の解決方法になります。

■ 貸借対照表

勘定科目コード	勘定科目	金額(資産)	金額(負債)
700	X	450	-450
1000	Z	25000	-25000
1001	あ	8100	-8100
1020	い	0	0
1891	う	1200	-1200
1999	え	500000	-500000
2000	カ	-120000	120000
2888	キ	7000	-7000
2999	ク	0	0
3000	た	0	0
3001	ち	-10000	10000
3002	つ	-14000	14000
3003	て	0	0
8999	A	4000	-4000
9000	＋	5500	-5500
9001	ー	450	-450
9002	＊	3200	-3200
9003	？	-8000	8000
9004	￥	15000	-15000
9005	～	0	0
9006	＃	-2500	2500
9999	！	54321	-54321

だとして
まずは、条件を入力するためのテーブル T_条件を作ります。
`sql
CREATE TABLE T_条件
(

      rid         AUTOINCREMENT  PRIMARY KEY
    , 種別        INT            NOT NULL
    , コードfrom  INT            NOT NULL
    , コードto    INT            NOT NULL
);


　
■ テーブルデータ

| rid | 種別 | コードfrom | コードto |
|-----|------|------------|----------|
| 1   | 0    | 1001       | 1999     |
| 2   | 0    | 9002       | 9002     |
| 3   | 0    | 9004       | 9005     |
| 4   | 1    | 2000       | 3001     |
| 5   | 2    | 4000       | 4999     |
| 6   | 3    | 5000       | 5999     |

( 続き )

次に、勘定科目コード、種別、金額の抽出条件全てを満たす
レコードを表示するためのクエリを作ります。
( SQLの結果に Q_表示対象という名前を付けて保存します )

SELECT y.種別
     , x.勘定科目コード
     , x.勘定科目
     , x.[金額(資産)] As cost
     , x.[金額(負債)] As revenue
FROM 貸借対照表 x
   , T_条件     y
WHERE x.勘定科目コード Between y.コードfrom And y.コードto
  AND y.種別 Between 0 And 1
  AND Abs( x.[金額(資産)] ) > 0 ;

種別	勘定科目コード	勘定科目	cost	revenue
0	1001	あ	8100	-8100
0	1891	う	1200	-1200
0	1999	え	500000	-500000
1	2000	カ	-120000	120000
1	2888	キ	7000	-7000
1	3001	ち	-10000	10000
0	9002	＊	3200	-3200
0	9004	￥	15000	-15000

　
■ 仕上げのSQL

SELECT Max( IIf( q.種別 = 0, q.勘定科目コード ) )  As ex1
     , Max( IIf( q.種別 = 0, q.勘定科目 ) )        As ex2
     , Max( IIf( q.種別 = 0, q.cost ) )            As 金額_資産
     , Max( IIf( q.種別 = 1, q.勘定科目コード ) )  As 勘定科目コード
     , Max( IIf( q.種別 = 1, q.勘定科目 ) )        As 勘定科目
     , Max( IIf( q.種別 = 1, q.revenue ) )         As 金額_負債
FROM 
(
    SELECT x.種別
         , x.勘定科目コード
         , x.勘定科目
         , x.cost
         , x.revenue
         , Count(1) As gnum
    FROM Q_表示対象 x
       , Q_表示対象 y
    WHERE x.種別 = y.種別
      AND x.勘定科目コード >= y.勘定科目コード
    GROUP BY x.種別
           , x.勘定科目コード
           , x.勘定科目
           , x.cost
           , x.revenue
) q
GROUP BY gnum
ORDER BY gnum ;

　
■ 結果

ex1	ex2	金額_資産	勘定科目コード	勘定科目	金額_負債
1001	あ	8100	2000	カ	120000
1891	う	1200	2888	キ	-7000
1999	え	500000	3001	ち	10000
9002	＊	3200
9004	￥	15000

( おまけ )

>> 5reito117さんの SQL を添削した正解例は、以下になります。
( 前回申し上げたとおり、煩雑で汚い SQL 文になりますから、この記述はお勧めしません )
　

SELECT Max( IIf( q.種別 = 0, q.勘定科目コード ) )  As ex1
     , Max( IIf( q.種別 = 0, q.勘定科目 ) )        As ex2
     , Max( IIf( q.種別 = 0, q.cost ) )           As 金額_資産
     , Max( IIf( q.種別 = 1, q.勘定科目コード ) )  As 勘定科目コード
     , Max( IIf( q.種別 = 1, q.勘定科目 ) )        As 勘定科目
     , Max( IIf( q.種別 = 1, q.revenue ) )        As 金額_負債
FROM
(
    SELECT IIf( x.勘定科目コード \ 1000 IN ( 1, 9 ), 0, 1 ) As 種別
         , x.勘定科目コード
         , x.勘定科目
         , x.[金額(資産)]                                  As cost
         , x.[金額(負債)]                                  As revenue
         , Count(1)                                       As gnum
    FROM 貸借対照表 x
       , 貸借対照表 y
    WHERE ( 
              ( x.勘定科目コード Between 1001 And 1999 )
                  OR
              ( x.勘定科目コード Between 2000 And 3001 )
                  OR
              x.勘定科目コード IN ( 9002, 9004, 9005 )
          )
      AND ( 
              ( y.勘定科目コード Between 1001 And 1999 )
                  OR
              ( y.勘定科目コード Between 2000 And 3001 )
                  OR
              y.勘定科目コード IN ( 9002, 9004, 9005 )
          )
      AND IIf( x.勘定科目コード \ 1000 IN ( 1, 9 ), 0, 1 )
              = IIf( y.勘定科目コード \ 1000 IN ( 1, 9 ), 0, 1 )
      AND x.勘定科目コード >= y.勘定科目コード
      AND Abs( x.[金額(資産)] ) > 0
      AND Abs( y.[金額(資産)] ) > 0
    GROUP BY x.勘定科目コード
           , x.勘定科目
           , x.[金額(資産)]
           , x.[金額(負債)]
) q
GROUP BY gnum
ORDER BY gnum ;

mayu様
いつも回答を頂き誠にありがとうございます。
時間遅くなりましたが無事できました。

でも不思議に思ったのは、今回(おまけではない方)は新しく『T_条件』というテーブルを追加してクエリを作成しました。
クエリは結合してテーブル同士をつなぎ合わせるのが通常ですが、これは『T_条件』と『貸借対照表』のテーブルが独立した状態で、結合のような事が出来て条件通りのデータが返されているという事です。

where条件を除けば特に大きな変わりはないはずなのに、何故このような独立したテーブルの状態で、望んだ結果が返されるのでしょうか？

『T_条件』と『貸借対照表』のテーブルが独立した状態で、
結合のような事が出来て条件通りのデータが返されているという事です。

SQL におけるテーブル同士の結合は
イコールだけではなく、
<= や >= や <> など、他の比較演算子も使えます。

ただ、Access のデザインビューでは
こういった演算子を用いて結合するクエリを作成することは出来ず、
デザインにおいても、グラフィカルな表示はできません。
SQL の扱いにおいて、デザインビューの機能や能力はかなり限定されたものになります。

また、オブジェクト間における結合の種類において
内部結合は FROM 句だけではなく、WHERE 句に記述することが可能で
( 外部結合は、FROM 句で JOIN による結合が必須 )
私が記述した SQL は、デザインビューで表示できるよう、
WHERE句に記述する方法を採用しています。

したがって、>> 7における Q_表示対象の SQL は、
以下のような記述を用いても、同様の結果を得ることが可能です。

SELECT y.種別
     , x.勘定科目コード
     , x.勘定科目
     , x.[金額(資産)] As cost
     , x.[金額(負債)] As revenue
FROM 貸借対照表    x
INNER JOIN T_条件  y
        ON ( x.勘定科目コード Between y.コードfrom And y.コードto )
WHERE y.種別 Between 0 And 1
  AND Abs( x.[金額(資産)] ) > 0 ;

SELECT y.種別
     , x.勘定科目コード
     , x.勘定科目
     , x.[金額(資産)] As cost
     , x.[金額(負債)] As revenue
FROM 貸借対照表    x
INNER JOIN T_条件  y
        ON x.勘定科目コード >= y.コードfrom
       AND x.勘定科目コード <= y.コードto
WHERE y.種別 Between 0 And 1
  AND Abs( x.[金額(資産)] ) > 0 ;

mayu様
いつも回答頂きありがとうございます。

まさか、where句でも結合が出来るなんて知りませんでした。
SQLは覚える事がいっぱいありますけね。
自分もmayu様のように自分で問題解決できるようになりたいです。

改めてありがとうございました。

タグリスト

VBA (389) Office2016 (294) フォーム (243) Office2021 or 2019 (224) クエリ SQL (220) テーブル (139) レポート (130) Office2013 (57) Office2010 (52) Microsoft365 (49)

Markdown テーブル生成

Accessのテーブルやクエリのデータを貼り付ける場合は下記で、Markdown書式のテーブルに変換して貼り付けてください。

Markdown Tables generator

Microsoft Access 掲示板

同じ内容のフィールド列に別々の結果を表示したい&数値がゼロ0の列は非表示にしたい

最新トピック

タグリスト

Markdown テーブル生成