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

当月と前年同月の売上額をひとつのテーブルに並べて表示。 (Access2010)

こちらのフォームには、日付を入力する4つののテキストボックスがあり、上段のテキストボックスには、当月売上の期間の始まりと終わりの日付を指定し、下段のテキストボックスには、前年同月の売上の期間の始まりと終わりの日付を指定します。

フォーム

そして、「売上集計」ボタンをクリックすると、次のように前年同月の売上と当月売上の日計表をひとつのテーブルに並べて表示されるようにしていきます。

クエリ

テーブル作成の流れ

  1. 抽出期間を指定するクエリ
  2. 日付から「日」の値を取り出す
  3. 「日」フィールドの値をテーブルに追加
  4. 「日付」「売上額」フィールドの値をテーブルに追加
  5. ボタンクリック時イベントにコードを記入

抽出期間を指定するクエリ

では、選択クエリを使って、売上データの中から、これらのテキストボックスに示された期間の売上を抽出します。クエリのデザイングリッドに、テーブル「T_売上」から、「日付」フィールドと「売上額」フィールドを表示させます。そして、「日付」フィールドの「抽出条件」のところに次のように、2つのテキストボックスを指定するようにします。

>=[Forms]![F_main]![テキスト1] And <=[Forms]![F_main]![テキスト2]

クエリデザイン

この[テキスト1]は、当月の開始日付、[テキスト2]は当月の終了日付を入力するテキストボックス名です。このクエリを実行するときは、フォーム[F_main]をフォームビューで開いて、実際にテキストボックスに開始と終了の日付を入れておく必要があります。

次に、リボンにある、「集計」ボタンをクリックします。すると、クエリのデザイングリッドに「集計」欄が1行追加されます。今回は日付ごとに売上額をまとめていくので、「日付」は「グループ化」、「金額」は「合計」を選びます。そうすることで、日付ごとの合計売上金額を表示することができます。では、クエリ名を「Q_当月売上」として、保存します。

クエリデザイン

ここで、「Q_当月売上」をもう一度、デザインビューで開くと、「売上額」のフィールド名が「売上額の合計:売上額」となっています。そこで、このコロンの左側を「当月売上」に変更します。後でこのフィールドを呼び出すときには、「当月売上」という名前を使いますので、わかりやすく簡潔な名前に変更しておきます。

クエリデザイン

「テキストボックスで指定した期間のデータを抽出する。」こちらもぜひ参考にしてください。

日付から「日」の値を取り出す

次に、「日付」フィールドから「年月日」の「日」の部分を取り出して、別のフィールドに表示させます。

Day([日付])

と、フィールド名の欄に入力します。「Enter」キーを押すと、コロンの左側に「式1」と表示されるので、この部分を任意の名前、ここでは「日」に変更します。

クエリデザイン

そして、「日付」フィールドの名前は、「当月日付:日付」と変更しておきます。フィールド名を変更するときは、変更前の元の名前を残しておいて、コロンで挟むようにします。そしてこの「日」フィールドは、デザイングリッドの一番左側に移動させました。

クエリデザイン

次に、前年売上のクエリについても同じように作ります。

「日付」フィールドの抽出条件のテキストボックス名に今度は、前年売上の開始日と終了日を入れる[テキスト3]と[テキスト4]を入力するようにします。

>=[Forms]![F_main]![テキスト3] And <=[Forms]![F_main]![テキスト4]

「売上額の合計」フィールドの名前は、「前月売上」としておきます。「日付」フィールド名は、「前月日付:日付」としておきます。Day([日付])フィールドの名前は、「Q_当月売上」と同じ「」としておきます。クエリ名は「Q_前年売上」としておきます。

クエリデザイン

「日」フィールドの値をテーブルに追加

では、この「Q_当月売上」クエリと「Q_前年売上」クエリをひとつのテーブルにまとめていこうと思います。まずは、新たにテーブルをひとつ作成します。テーブル名は「T_売上A」とし、5個のフィールドを作り、データ型はこのように設定します。

テーブルデザイン

まず、「Q_前年売上」の「日」フィールドのデータを追加クエリを使って、「T_売上A」の「日」フィールドに入れます。これは、「日付」から「日」の部分だけを抜き出すことで、「の値を基準に、前年と今年の同じ日を同じ行に揃えて入れるためです。この追加クエリの名前は「Q_ruikei1」としておきます。

追加クエリの方法はこちら「テーブルにレコードを追加する」も参考にしてください。

次に、「Q_当月売上」の「日」フィールドのデータのうち、「Q_前年売上」の「日」フィールドにはなかった「日」のみを「T_売上A」の「日」フィールドに追加します。これは、今回想定している売上データは、平日(月曜から金曜)に発生しているものとしているため(土曜、日曜は休み)、前年と当年では、データが発生している「日」が必ずしも一致していないという理由からこのようにしています。

ではここは、「Q_前年売上」の「日」フィールドと「Q_当月売上」の「日」フィールドとの差分を出すために、クエリウィザードから「不一致クエリウィザード」を使って作成していきます。

クエリウィザード

「新しいクエリ」ボックスから、「不一致クエリウィザード」を選択します。

不一致クエリ

「不一致クエリウィザード」に沿って、クエリを作っていきます。まず最初に「Q_当月売上」を選択します。

不一致クエリ

次に、比較対象となるテーブルを選択しますが、ここでは、「Q_前年売上」の「日」データを、すでに「T_売上A」の「日」フィールドに入れていますので、この「T_売上A」の「日」のデータを比較対象とします。ですので、「T_売上A」を選択します。

不一致クエリ

さらにウィザードを進めていき、最後に名前を付けて保存します。ここでは、「Q_sabun」としました。ここには「Q_当月売上」にだけある「日」の値が抽出されています。では、この「Q_sabun」のデータを「T_売上A」の「日」フィールドに追加します。「Q_前年売上」のときとおなじように追加クエリを作成します。クエリ名は、「Q_ruikei2」とします。

クエリ不一致クエリの作成はこちら「比較するテーブルに無いデータを抽出 」 も参考にしてください。

「日付」「売上額」フィールドの値をテーブルに追加

次は、前年の日付と売上額、それから、当月の日付と売上額も「T_売上A」に入れていきます。ここでまず、先ほどのクエリ「Q_前年売上」と「Q_当月売上」から、それぞれ、テーブル作成クエリを使って、新たなふたつのテーブルをつくります。ふたつのクエリの表示内容はそのままで、それをいったんテーブルに変換するのです。新たに作成するテーブル名は、それぞれ、「T_前年売上」、「T_当月売上」とします。テーブル作成クエリのクエリ名は「Q_ruikei3」、「Q_ruikei5」とします。

テーブル作成クエリテーブル作成クエリの方法はこちらも「クエリをもとに新たにテーブルを作成」 参考にしてください。

では、「T_前年売上」、「T_当月売上」テーブルができたところで、そのデータを「T_売上A」つまり、前年と当月の売上をまとめてひとつにするテーブルに格納していきます。先ほど、「T_売上A」の「日」フィールドにデータを入れました。ここでは、さらに、「前年日付」フィールドと「前年売上」フィールドにデータを格納していくために、更新クエリを使います。しかもここでは、SQL文を用いてクエリを作成してみたいと思います。

まず、リボンの「作成」タブから、「クエリデザイン」を選択すると、「テーブルの表示」ボックスが出ますが、それは、何もしないで閉じます。そして、リボンの「クエリツール」「デザイン」にある、クエリの種類に中から、「更新」を選択し、左端の「表示」を選択して、下に表示されるリストの中から、「SQLビュー」を選択します。

更新クエリデザインすると、真っ白な画面が出てきますので、そこにSQL文を書いていきます。そして、名前を付けて保存します。ここでは、「Q_ruikei4」とします。

SQL文

このSQL文で作った更新クエリをデザインビューから作った場合はこのようになります。

クエリデザイン

次に、「T_当月売上」の「当月日付」フィールド、「当月売上」フィールドの値を「T_売上A」のレコードに入れます。「T_当月売上」についても、「T_前月売上」と同じように更新クエリを使っていきます。クエリ名は、「Q_ruikei6」とします。

ここまで作成したクエリを順番に実行していくと、前年売上と当月売上をひとつの表にまとめた、「T_売上A」テーブルはこのような感じにできあがります。「日」フィールドについては、表示させなくてもいいので、選択クエリで、「日」フィールドを除いて後の4つのフィールドを表示させるようにします。クエリ名は「Q_ruikei11」としておきます。

テーブル

ボタンクリック時イベントにコードを記入

では、これまで作ったアクションクエリを順番に実行するために、フォームにある「売上集計」ボタンがクリックされた時のイベントプロシージャにVBAコードを書いていきます。「コマンドボタン」の「イベント」タブから、「クリック時」の右端にあるビルドボタン「…」をクリックします。

フォームデザイン

コードの記述方法は、Visual Basic EditorにVBAコードを記述する。も参考にしてください。

まず最初に、アクションクエリを実行するときに出てくる、メッセージを表示させないようにするために、

DoCmd.SetWarnings False

のコードを書きます。その次に、「コマンドボタン」をクリックするごとに、新たにテーブルを作成すようにするので、前に作ったテーブルは、最初に一旦すべて削除しておきます。テーブル「T_売上A」のレコードをすべて削除するクエリを作成します。ここでのクエリ名前は、「Q_ruikei0」とします。アクションクエリが一通り終われば、先ほど、非表示にしたメッセージを再び表示されるようにコードを書きます。

DoCmd.SetWarnings True

フォームの「売上集計」ボタンをクリックすると当月売上と前年同月売上の一覧表が表示されます。

クエリ

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

コメントを残す

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

コメントフィード

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

PAGE TOPへ戻る