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

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

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

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
https://hatenachips.blog.fc2.com/tb.php/68-137a79fb
該当の記事は見つかりませんでした。
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。