Accessで昨対比を作ってグラフ化する。開発16日目。
レポート機能のグラフ作成がわかってくるといろいろ作りたくなります。美容院向けCRMに売上昨対、客数昨対の推移を表すグラフをつけてみました。
これを作りるには、月ごとの売上と前年の売上を一つのレコードに並べたクエリを作らないといけないのですが、これがなかなか難し。いろいろなやり方があるのでしょうが、簡単でしっくり来た方法を以下のサイトから参考にさせていただきました。
「Access実践サンプル」http://psp8155.blog13.fc2.com/blog-entry-194.html
管理者のむさしさんも書いてらっしゃいますが、Excelでは簡単にできることが、意外と難しいのです。ただ前年の数字を横に並べたいだけなのに。
例に従って、まずは二つのクエリを作ります。
クエリ１をつくる
一つ目のクエリには昨対比１と名付けました。売上の合計を「本年売上」、売上のカウントを「本年客数」としました。一番右のWhere条件は売上が0円のものは除外するために0より大きいことを条件にしてあります。
クエリ２をつくる
二つ目のクエリには昨対比２と名付けました。上の結果表示との違いは連結列だけです。昨対比２のクエリのほうが1年進んでいます。これがズレをうまく作ってくれます。昨対比２では売上と客数のタイトル前に前年とつけているのもポイントです。
これが本命3つ目のクエリをつくる
最後に実際にグラフ表示につかう三つ目のクエリを作ります。クエリの名前は数字なしで「昨対比」このクエリが昨対比前年比を実現するための肝です。
小さくなってしまったので、拡大してみてください。見ての通りポイントは二つのクエリを3つ目のクエリで連結という項目をリレーションで結んでいるところです。むさしさんがおっしゃる通り、前年に数字がない場合に備えて片側向きのリレーションにしてあります。
これさえできていれば、あとはクエリの中で「本年÷前年」してやれば昨対比の出来上がり。グラフでパーセント表示させる際に不格好にならないようにRound関数を使って少数点2桁に丸めています。
最後の「表示月」というのはグラフのX軸に「〇月」と表示させるためのひと手間です。2列目の月をわざわざ「当月」に名前を変えて使っているのは、「月」のままだと二つのクエリのどちらを指定しているかわからないと拒否られたため。
あと、細かいことですが、「年」の抽出条件に 「Year(date())」と入れてて置くことで、1月から12か月間、営業日が進むにつれてグラフが成長する感じになります。
あとはグラフの設定で軸（項目）に「表示月」を選び、軸（Y軸）に「売上昨対（なし）」および「客数昨対（なし）」の2か所にチェックを入れてやります。
Accessのレポート機能にあるグラフ作成についてもう少し詳しく知りたい方は前回（開発14日目、15日目）の投稿を参考にしてくださいね。
2年分表示させたければもう一つ「Year(date())-1」をOR条件として付けくわえるのですが、表示月が2年間で同一になってしまうので、年にも「当年」のようなラベルをつけて、表示月:[当年]&[当月]のように年度が別行になるように工夫すると良いと思います。