Microsoft Access 掲示板

Microsoft Access 掲示板

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

11 コメント
views
4 フォロー

前回の同じ内容のフィールド列に別々の結果を表示したいの続きになります。
https://zawazawa.jp/ms-access/topic/131

前回は列の左側と右側で勘定科目コード別に分けたいというものでした。
これに付属して、金額(費用)及び金額(収益)の数値が0ならば非表示になるようにしたい。
・0で非表示になったら、データが上から押し上げていく形で表示されるようにしたい
・0があると非表示になるが、行全体が非表示になるのではなく、例えば左側の部分の数値が0になったら左側のみ非表示にしたい。

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

更新後、
xpr1000：Expr1001：金額(費用)：勘定科目コード：勘定科目：金額(収益)
4001：仕入：25000：5001：売上：3500
4004：減価償却費：500000：5008：有価証券利息：1300
4005：広告宣伝費：120000：5010：固定資産売却益：12000
4006：通信費：7000
4007：水道光熱費：10000
4010：旅費交通費：-14000
4012：消耗品費：4000

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

reito117
作成: 2020/02/17 (月) 09:49:35
通報 ...
1
mayu 2020/02/17 (月) 15:09:42 8b12d@61ad5

前回の回答、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 ;
2
reito117 2020/02/18 (火) 10:47:29

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

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

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

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

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

3
mayu 2020/02/18 (火) 14:26:22 a3615@61ad5

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番台 のコードそれぞれにグループ連番を付与することですが
累計計算も同時に出来たりしますから
以下にデモンストレーションを掲載しておきます。

4
mayu 2020/02/18 (火) 14:27:47 a3615@61ad5

( 続き )

■ 損益計算書

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

　
■ 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.勘定科目コード ;

　
■ 結果

勘定科目コード勘定科目costrevenuegnumtotal_costtotal_reve
4001仕入25000-250001250000
4004減価償却費500000-50000025250000
4005広告宣伝費120000-12000036450000
4006通信費7000-700046520000
4007水道光熱費10000-1000056620000
4010旅費交通費-140001400066480000
4012消耗品費4000-400076520000
5001売上-35003500103500
5004受取家賃70-70203430
5008有価証券利息-100100303530
5011償却債権取立益-120120403650
5
reito117 2020/02/21 (金) 10:04:53

回答ありがとうございます。
教えて頂いた通りにできました。
また、教えて頂いて恐縮ですが下記の様にするにはどうすればいよいか教えて頂きたく。
・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の列を見ると並びがきれいに並ばなくなります。

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

6
mayu 2020/02/21 (金) 15:01:49 修正 a3615@61ad5

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

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

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

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

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

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

■ 貸借対照表

勘定科目コード勘定科目金額(資産)金額(負債)
700X450-450
1000Z25000-25000
1001あ8100-8100
1020い00
1891う1200-1200
1999え500000-500000
2000カ-120000120000
2888キ7000-7000
2999ク00
3000た00
3001ち-1000010000
3002つ-1400014000
3003て00
8999A4000-4000
9000＋5500-5500
9001ー450-450
9002＊3200-3200
9003？-80008000
9004￥15000-15000
9005～00
9006＃-25002500
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     |
7
mayu 2020/02/21 (金) 15:03:45 修正 a3615@61ad5

( 続き )

次に、勘定科目コード、種別、金額 の抽出条件全てを満たす
レコードを表示するためのクエリを作ります。
( 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 ;

　

種別勘定科目コード勘定科目costrevenue
01001あ8100-8100
01891う1200-1200
01999え500000-500000
12000カ-120000120000
12888キ7000-7000
13001ち-1000010000
09002＊3200-3200
09004￥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 ;

　
■ 結果

ex1ex2金額_資産勘定科目コード勘定科目金額_負債
1001あ81002000カ120000
1891う12002888キ-7000
1999え5000003001ち10000
9002＊3200
9004￥15000
8
mayu 2020/02/22 (土) 23:14:23 a3615@61ad5

( おまけ )

>> 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 ;
9
reito117 2020/02/26 (水) 09:11:07

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

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

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

10
mayu 2020/02/26 (水) 15:02:02 a3615@61ad5

『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 ;
11
reito117 2020/02/28 (金) 09:08:24

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

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

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