MS Access Tips/Sample and VBA and Blog customize etc...

レポートでの複雑な集計、グループ化(会計帳票)

moug での質問で回答するためにサンプルを作成したので紹介します。

元ネタ 会計帳票

質問の概要は、

 コーラ売上      金額1
 スプライト売上    金額2
 期首在庫       金額3
 仕入          金額4
 期末在庫       金額5

というソースデータをもとに、下記のように帳票出力したいというものです。

 コーラ売上      金額1
 スプライト売上    金額2
  売上高計      金額1+2
 
 期首在庫       金額3
 仕入          金額4
 小計          金額3+4
 期末在庫       金額5
 
  売上原価      金額3+4-5
  売上総利益     金額1+2-3-4+5

実際の会計帳票はこのような単純なものではないと思いますが、シンプルなモデルで考え方が理解できれば応用が利くと思います。

難易度:

テスト用のサンプルとして下記のようなテーブルを用意しました。

科目マスタ
科目CD 科目名 CD1 CD2
1 コーラ売上 1 1
2 スプライト売上 1 1
3 期首在庫 2 2
4 仕入 2 2
5 期末在庫 2 3

大グループマスタ
CD1 大グループ名 演算区分1
1 売上高計 1
2 売上原価 -1

小グループマスタ
CD2 小グループ名 演算区分2
1   1
2 小計 1
3   -1

これらのマスタ以外に下記のトランザクション系のテーブルがあるとします。

会計出納
ID 日付 科目CD 金額 摘要
1 2008/01/01 3 200  
2 2008/01/01 1 100  
3 2008/01/02 2 200  
4 2008/01/03 4 300  
5 2008/12/31 5 250  

マスタテーブルの意味を解説しますと、大グループマスタの意味は理解しやすいですね。売上高はプラス項目、売上原価はマイナス項目という意味で、金額とこれをかけたものを集計(Sum)すれば、売上高-売上原価 で、売上総利益 になります。小グループマスタは、小計を出す項目と出さない項目を決定するものです。小グループ名に入力がない場合は、レポートのVBAでこのグループフッターを出力しないように制御します。また、大グループ内でプラス項目なのかマイナス項目なのか指定します。「期末在庫」は「売上原価」内では、マイナス項目であるという意味を持たせています。これと金額をかけたものを集計(Sum)すると、[期首在庫]+[仕入]-[期末在庫] = [売上原価] という計算ができます。

次に、年度集計を出力するクエリを作成します。 

 クエリ名: Q年度会計

このクエリの出力結果は、

Q年度会計
ID CD1 大グループ名 CD2 小グループ名 科目名 金額の合計 演算区分2 演算区分1
1 2 売上原価 2 小計 期首在庫 200 1 -1
2 1 売上高計 1   コーラ売上 100 1 1
3 1 売上高計 1   スプライト売上 200 1 1
4 2 売上原価 2 小計 仕入 300 1 -1
5 2 売上原価 3   期末在庫 250 -1 -1

このクエリをレポートのレコードソースにします。

「並べ替え/グループ化の設定」で、
  
フィールド/式 並べ替え順序 グループフッター
------------------------------
CD1       昇順    はい
CD2       昇順    はい
ID        昇順    いいえ
 
 
コントロールソース  小グループ名
コントロールソース  =Sum([金額])
  
CD1フッターには、下記の2つ。
  
コントロールソース  大グループ名
コントロールソース  =Sum([金額]*[演算区分2])
  
レポートフッターには、下記の2つを配置。
ラベル
 標題  売上総利益
テキストボックス
 コントロールソース  =Sum([金額]*[演算区分1]*[演算区分2])

CD2フッターのフォーマット時のイベントプロシージャを下記のように記述。

以上で、下記のようなレポート出力になります。

 

下記リンクからサンプル MDB をダウンロードできます。
RptAccountBook.zip (Access 2002-2003 形式 - 18kb)
RptAccountBook2k.zip (Access 2000 形式 - 17kb)

拍手する

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/68-137a79fb
該当の記事は見つかりませんでした。