このエントリーをはてなブックマークに追加

期首在庫、仕入、売上、粗利から、期末在庫を計算。(Access)

期首在庫データ、仕入データ、売上データ、粗利データの入ったテーブルを使って、期末在庫の計算をしていきます。まずは、期末在庫を求めるための「T_集計」テーブルを用意します。今回は、「店ID」ごとに集計をしていきますので、「店ID」フィールドも用意します。「期末在庫」については、最後にクエリを作って、クエリで計算します。(集計の期間は1ヶ月間としています。)

テーブルデザイン

作成の流れ

  1. 「期首在庫」テーブル、「仕入」「売上」クエリを作成する。
  2. 各データを「T_集計」に格納する。
  3. Null値のデータを「0」に変換する。
  4. 期末在庫を算出する。

「期首在庫」テーブル、「仕入」「売上」クエリを作成する。

「T_期首在庫」テーブルを用意します。今、「店ID」フィールドに「1」、「2」のデータがあり、期首在庫はともに、「¥0」としています。

テーブル

「Q_仕入」クエリを用意します。「Q_仕入」クエリは、仕入テーブルに入っている仕入データを1ヶ月間抽出して、「店ID」ごとに仕入金額の合計を集計したものです。「店ID」フィールドには、新たに「4」、「5」の「店ID」が発生しています。

クエリ

「Q_売上」クエリを用意します。「Q_売上」クエリは、売上テーブルに入っている売上データを1ヶ月間抽出し、「店ID」ごとに、売上金額と粗利金額の合計を集計したものです。「1」、「4」、「5」の「店ID」の売上が発生しています。

テーブル

各データを「T_集計」に格納する。

では、「T_期首在庫」、「Q_仕入」、「Q_売上」の順に、「T_集計」にデータを格納していきます。

  1. 「T_期首在庫」の「期首」、「店ID」、「期首在庫」フィールドのデータを追加クエリで、「T_集計」に入れる。追加クエリ名は「Q_kisyu1」。
  2. 「Q_仕入」の「店ID」フィールドのデータを追加クエリで、「T_集計」に入れる。追加クエリ名は「Q_siire1」。
  3. 「Q_仕入」からテーブル作成クエリで、「Q_仕入」と同じ内容の「T_siire」テーブルを作成する。テーブル作成クエリ名は「Q_siire2」。
  4. 「T_siire」の「仕入金額」フィールドのデータを更新クエリで、「T_集計」に入れる。更新クエリ名は「Q_siire3」。
  5. 「Q_売上」の「店ID」フィールドのデータを追加クエリで、「T_集計」に入れる。追加クエリ名は「Q_uri1」。クエリデザイン
  6. 「Q_売上」からテーブル作成クエリで、「Q_売上」と同じ内容の「T_uriage」テーブルを作成する。テーブル作成クエリ名は「Q_uri2」。テーブル
  7. 「T_uriage」の「売上金額」フィールド、「粗利金額」フィールドのデータを更新クエリで、「T_集計」に入れる。更新クエリ名は「Q_uri3」。クエリデザイン

上記の1~4については、2つのテーブルのデータを別の1つのテーブルにまとめる。のページで詳しく載せています。ここまでの「T_集計」テーブルは、このようになります。

テーブル

Null値のデータを「0」に変換する。

「T_集計」の中には、データの無かった箇所が、「Null値」となっています。このままだと、期末在庫の計算ができないので、更新クエリを使って、「Null値」のデータを「0」に更新します。期首の日付については、今回、フォーム上に、期首と期末それぞれの日付を入力するテキストボックスを、2つ用意して、仕入、売上の期間を抽出しているので、そのテキストボックスの日付を参照するようにしています。

  1. 「期首」の「Null値を」「0」に更新する。クエリ名は、「Q_kisyu2」。
  2. 「期首在庫」の「Null値を」「0」に更新する。クエリ名は、「Q_kisyu3」。
  3. 「仕入金額」の「Null値を」「0」に更新する。クエリ名は、「Q_siire4」。
  4. 「売上金額」の「Null値を」「0」に更新する。クエリ名は、「Q_uri4」。
  5. 「粗利金額」の「Null値を」「0」に更新する。クエリ名は、「Q_uri5」。

ここまでの「T_集計」は、このようになります。

テーブル

期末在庫を算出する。

新たに選択クエリを作成して、「T_集計」の各フィールドをデザイングリッドに追加したあと、「期末在庫」の計算式、期末在庫:[期首在庫]+[仕入金額]-[売上金額]+[粗利金額]を追加します。

クエリデザイン

最終的に、期末在庫は、「Q_syukei1」として、このようになります。

クエリ

これまでの一連のアクションクエリをフォームのボタンのクリック時イベントに記述すれば、この「Q_syukei1」クエリを作成することができます。

フォーム

この集計表クエリを作る際には、前のデータと重複しないように、まず最初に「T_集計」データを全部削除してから、作るようにします。削除クエリ名は「Q_sakujo」にしています。

クエリデザイン
テーブル作成クエリ実行時には、このようなメッセージが出ます。

メッセージ

これを非表示にしたい場合は、「DoCmd.SetWarnings False」というコードを最初に書き加えます。そして、最後に「DoCmd.SetWarnings True」というコードを書いて、元に戻すようにします。

Private Sub コマンド1_Click()
DoCmd.SetWarnings False

DoCmd.OpenQuery "Q_sakujo"     'T_集計レコードの削除	
DoCmd.OpenQuery "Q_kisyu1"     '期首日付、店ID、期首在庫の追加	
DoCmd.OpenQuery "Q_siire1"     '仕入の店IDの追加	
DoCmd.OpenQuery "Q_siire2"     '仕入テーブルの作成	
DoCmd.OpenQuery "Q_siire3"     '仕入金額の更新	
DoCmd.OpenQuery "Q_uri1"       '売上の店IDの追加		
DoCmd.OpenQuery "Q_uri2"       '売上粗利テーブルの作成		
DoCmd.OpenQuery "Q_uri3"       '売上金額、粗利金額の更新
DoCmd.OpenQuery "Q_kisyu2"     '期首Null値を期首日付に更新
DoCmd.OpenQuery "Q_kisyu3"     '期首在庫 Null値を0に更新
DoCmd.OpenQuery "Q_siire4"     '仕入金額	Null値を0に更新		
DoCmd.OpenQuery "Q_uri4"       '売上金額 Null値を0に更新						
DoCmd.OpenQuery "Q_uri5"       '粗利金額 Null値を0に更新	

DoCmd.OpenQuery	 "Q_syukei1"   '最終集計クエリを開く

DoCmd.SetWarnings True
End Sub

 

このエントリーをはてなブックマークに追加

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

コメントフィード

トラックバックURL : http://gyoumuka.work/wp-trackback.php?p=2505

PAGE TOPへ戻る