Microsoft Access 掲示板

同じ内容のフィールド列に別々の結果を表示したい

9 コメント
views
4 フォロー

図を見て頂きたいと思います。

画像

expor1000と勘定科目コードは同じです。
EXpor1001と勘定科目のフィールドは同じです。
金額(費用)と金額(収益)も同じ内容ですが計算の向きだけ違います。

これを下のデータの様に、

Expr1000Expr1001金額(費用)勘定科目コード勘定科目金額(収益)
4001仕入250005001売上3500
4002売上原価05002商品売買益0
4003発送費05003受取手数料0
4004減価償却費5000005004受取家賃0
4005広告宣伝費1200005005受取利息0
4006通信費70005006受取配当金0
4007水道光熱費100005007雑益0
4008租税公課05008有価証券利息0
4009給料05009有価証券売却益0
4010旅費交通費-140005010固定資産売却益0
4011支払保険料05011償却債権取立益0
4012消耗品費4000
4013雑損0
4014貸倒引当金繰入0
4015貸倒損失0
4016支払利息0
4017手形売却損0
4018有価証券売却損0
4019固定資産売却損0

 
左側のexpor1001と金額(費用)は、勘定科目コードが4000番台のみ表示して、
右側の勘定科目コードと金額(収益)は、
勘定科目コードが5000番代のみ表示したいです。

どのようにすればいいか教えて頂きたくお願い致します。

※掲示板管理者(hatena)が読みやすくするために書式を改善しました。

reito117
作成: 2020/01/24 (金) 11:08:15
最終更新: 2020/01/25 (土) 09:35:35
通報 ...
1

どのようにすればいいか教えて頂きたくお願い致します。

3パターンの SQL を載せておきます。

■ 集計クエリ

SELECT Max( IIf( q.勘定科目コード < 5000, q.勘定科目コード ) )  As ex1
     , Max( IIf( q.勘定科目コード < 5000, q.勘定科目 ) )        As ex2
     , Max( IIf( q.勘定科目コード < 5000, q.[金額(費用)] ) )    As 金額_費用
     , Max( IIf( q.勘定科目コード > 4999, q.勘定科目コード ) )  As 勘定科目コード
     , Max( IIf( q.勘定科目コード > 4999, q.勘定科目 ) )        As 勘定科目
     , Max( IIf( q.勘定科目コード > 4999, q.[金額(収益)] ) )    As 金額_収益
FROM 損益計算書 q
WHERE q.勘定科目コード Between 4000 And 5999
GROUP BY q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 )
ORDER BY q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 ) ;

 
■ 自己結合

SELECT x.勘定科目コード As ex1
     , x.勘定科目       As ex2
     , x.[金額(費用)]   As 金額_費用
     , y.勘定科目コード
     , y.勘定科目
     , y.[金額(収益)]   As 金額_収益
FROM      損益計算書 x
LEFT JOIN 損益計算書 y
ON
( 
      ( x.勘定科目コード Between 4000 And 4999 ) 
  AND
      x.勘定科目コード = y.勘定科目コード - 1000
)
ORDER BY x.勘定科目コード ;

 
■ 完全外部結合

SELECT y.勘定科目コード As ex1
     , y.勘定科目       As ex2
     , y.[金額(費用)]   As 金額_費用
     , z.勘定科目コード
     , z.勘定科目
     , z.[金額(収益)]   As 金額_収益
FROM
(
    (
        SELECT 勘定科目コード
        FROM 損益計算書
        WHERE 勘定科目コード Between 4000 And 4999
        UNION
        SELECT 勘定科目コード - 1000
        FROM 損益計算書
        WHERE 勘定科目コード Between 5000 And 5999
    ) x
    LEFT JOIN 損益計算書 y
           ON x.勘定科目コード = y.勘定科目コード
)
LEFT JOIN 損益計算書 z
       ON x.勘定科目コード = z.勘定科目コード - 1000
ORDER BY x.勘定科目コード ;
2

2020/01/24 (金) 15:00:34 に私が回答した後
2020/01/24 (金) 19:11:55 に投稿内容が編集されているようですが
その上で未解決ということは、もしかすると SQLの扱いが初めて なのでしょうか。

SQLは、フォームやレポートのレコードソースへも直に記述できるため
記述場所としては、色々あるわけですが

クエリを作るという状況において
SQLをどこに記述すればいいのか わからない
ということでしたら、以下をご覧になるといいでしょう。
 

クエリ SQL文でクエリを編集する: ACCESSとVBAとシステム開発
 ACCESSのクエリデザイン画面は、SQLの構文が分からなくても、感覚的な操作でクエリを作成することが出来る、とても便利なツールです。  SQLを直接書けるレベルになっても、キーボードからテーブル名や項目名を入力しなくて良いので、ちょっとした検索や集計をするのに便利です。  SQLServerやOracleなどの本格的なデータベースを使用するようになっても、ACCESSのクエリの便利さを一度覚えると、ACCESSが手放せなくなります。  簡単なクエリを作る場合、ACCESSのクエリデザインの画面はとても便利ですが、扱う項目数やテーブル数が多くなったり、複雑な条件を入力する場合や、サブクエリを利用したい場合は、デザイン画面ではやりにくいことがあります。  複雑な条件の場合は、グリッド..
ACCESSとVBAとシステム開発

 
なお、

損益計算書

勘定科目コード勘定科目金額(費用)金額(収益)
4001仕入25000-25000
4006通品費7000-7000
4010旅費交通費-1400014000
5006受取配当金-100100
5008有価証券利息00

 
上記のように、勘定科目コードの属性毎に欠番がある場合、
 

Expr1000Expr1001金額(費用)勘定科目コード勘定科目金額(収益)
4001仕入25000
4006通品費70005006受取配当金100
5008有価証券利息0
4010旅費交通費-14000

 
下3桁で完全外部結合するのではなく
 

Expr1000Expr1001金額(費用)勘定科目コード勘定科目金額(収益)
4001仕入250005006受取配当金100
4006通品費70005008有価証券利息0
4010旅費交通費-14000

 
同属の科目を昇順に並べるだけの場合は、SQLのロジックにもう一工夫必要になります。
 

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.勘定科目コード
    GROUP BY x.勘定科目コード
           , x.勘定科目
           , x.[金額(費用)]
           , x.[金額(収益)]
) q
GROUP BY gnum
ORDER BY gnum ;

 
ただし、データベースというのは 本来
第三者が見ても「 行単位でデータ構造を把握できる形 」になっている必要があり、
関連のない複数のデータが同じ行に並んでいるのは、不自然と言っていいでしょう。

投稿文のタグに クエリ・フォーム・レポート の3つが付与されていることから
どのオブジェクトにおいても段組で表示したい のだと想像しますが
コードの体系別に2列表示したいだけなら、レポートのみで行うことをお薦めします。

3

mayuさん、いつも回答ありがとうございます。
質問の編集は私が管理者権限で行いました。見やすくする為にテーブルをマークダウン書式にしました。

4

hatenaさん、こんにちは。
各方面でのご活躍、いつも拝見させていただいております。
管理者権限での表の編集、承知いたしました。わざわざのご連絡、ありがとうございます。

5
reito117 2020/01/27 (月) 11:48:18

mayu様

回答ありがとうございます。
正に思う通りの内容を記載して頂いた上に、様々教えて頂き大変恐縮です。

SQLはわずかながらに読める程度なので後学の為に教えて頂きたいです。
教えて頂いた3つのうちの1つ、集計クエリでのGROUP BY句の部分です。
GROUP BY q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 )

並びの肝の部分だと思うのですが、この条件式でなぜこんなきれいな並びになるかわかりません。
色々と調べてみてもわからないので教えて頂きたくお願い致します。

6

GROUP BY q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 )

並びの肝の部分だと思うのですが、
この条件式でなぜこんなきれいな並びになるかわかりません。

勘定科目コードの 4000番台と5000番台の下3桁同士を
同じグループ( 母集団 )に所属させるための演算になります。
例えば

  ・ 4000 と 5000 が同じグループ ( 4000 - 0 = 4000, 5000 - 1000 = 4000 )
  ・ 4011 と 5011 が同じグループ ( 4011 - 0 = 4011, 5011 - 1000 = 4011 )
  ・ 4999 と 5999 が同じグループ ( 4999 - 0 = 4999, 5999 - 1000 = 4999 )

になります。
( 並び替えも演算結果の数値順になるよう、ORDER BY句に同様の式を記述しています )

したがって、

GROUP BY q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 )
ORDER BY q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 )

GROUP BY q.勘定科目コード Mod 1000
ORDER BY q.勘定科目コード Mod 1000

 
と記述しても 同様の結果を得ることが可能です。

7
reito117 2020/01/30 (木) 10:07:20

度重なる質問に対して誠にありがとうございます。
正直なところ力不足でまだ理解が追い付いていない部分もありますが、理解が出来る様に頑張っていきます。
また何かありましたら力を貸して頂ければ幸いです。

8
hiroton 2020/01/30 (木) 11:54:58 7cddf@f966d

集計直前の状態をイメージできるとわかると思いますよ
SQLからGROUP BY句を消し、集計関数のMAXもなくすと

ex1ex2金額_費用勘定科目コード勘定科目金額_収益gEX
4001仕入250004001
5001売上35004001
4002売上原価04002
5002商品売買益04002
4003発送費04003
5003受取手数料04003

こんな感じになります
gEXはグループ化のために作った計算式をフィールドに表示してみるとってことです

gEXでグループ化し、各列MAXをとれば、MAXといっても2レコードの内片方にしか値がないので必ずその値を拾ってきます

並べたいレコードがちょうどセットになるようなgEXを作ればいいので
q.勘定科目コード - IIf( q.勘定科目コード < 5000, 0, 1000 )GROUP BYだから何か特別なことをしているということはありません

ついでにORDER BYも同じ式を使っているので4001からきれいに並んでいるわけですね

9
reito117 2020/01/31 (金) 11:01:53

回答ありがとうございます。

まさか回答頂けると思わなかったので驚きました。
でも教えて頂いたおかげで理解が出来ました。

gEXはグループ化のために作った計算式をフィールドに表示してみるとってことです

これで理解が出来ました。
group byの列は2つ同じ勘定科目コードを作成し、それをグループ化することで一行にできる。

それと同時にこのような事をすぐにわかる事が出来るのが本当にすごいと思いました。
自分もこのような難しい内容をすぐに出来る様になってみたいです。

本当に感謝しかありませんが、改めてありがとうございます。