カテゴリー: ひとこと

Accessでの開発。顧客管理と顧客ごとの売上入力ができると、それをまとめたくなります。日別、月間の売上グラフ。担当者ごとの売上一覧。客数や客単価の分析。せっかくデータがあるのに使わないのはもったいない。

百聞は一見に如かず。できたのはこんな感じのダッシュボードです。（当たり前のように言ってますが、ダッシュボードってなんやねん？ってことですが、まあ情報を一目でわかるようなものってイメージもってます。）

こういうグラフが簡単にできるのがAccessの良いところですが、あまり使われているところをみかけませんね。ともかく、グラフを作るにはデータが必要ですから、その元をクエリで作ってやります。

このクエリの中身は以下のようになっています。

とてもシンプルです。凝っているのは抽出条件につかっているDateSerialでしょう。DateSerialはかっこの中に、年、月、日が並びます。Year、Month関数は年月日から年、月も数字だけを取り出す関数。

よく見るとYearの後ろで１を引いてます。日のところには１を直接入力しています。つまり、1年前のその月の1日を示しています。＞＝なので、昨年同月の1日以降を指定していることになります。

グラフをつくるにはレポートをつかう

グラフの追加についてはいまいちわかりにくい場所にあります。まずはレポートを新規作成します。リボンメニューの「作成」の中にある「空白のレポート：をつかえばOKです。

ここがポイント！レポートが開けたら、タブ部分を右クリックして「デザインビュー」で開きます。

「グラフの挿入」をクリックすると、挿入できるグラフの種類が現れるので、適当なグラフを選択します。どこに配置するか選べるようになりますので、画面の適当な場所においてやります。サイズもグラフの種類もあとでプロパティで修正できるので適当でよいです。

最初に表示されているグラフはダミーなので、右側に表示された「グラフの設定」でデータソースを選択します。データソースは先ほどクエリで用意した日付ごとの売上一覧です。「Dash_日別売上」という名前をつけていたので、選択してやります。

データソースを選択すると、軸の選択ができるようになります。

軸（項目）というのは横軸です。「売上日（月）」となっています。下にある軸（Y軸）が縦軸。こちらは「売上（合計）」にします。

つまり、これだけで1か月ごとの売上合計をグラフにできるわけです。感がよい方ならもうお分かりですね？　合計以外にも「カウント」や「平均」が選択できることを。つまり同じ手順、同じデータで売上だけでなく客数のグラフを作ることができました。

こんどはグラフの設定から「書式」を選んでやります。「データラベルを表示」にチェックをいれるだけで、月ごとの売上合計が表示されました。グラフの色もここから変更することができます。

グラフのタイトルなどは「プロパティシート」で編集します。「グラフのタイトル」という項目をかえてやると、グラフのタイトルが変化します。凡例は今回のグラフでは要素が１つで役にたたないので、「いいえ」に切り替えてやると、グラフが大きくなって見やすくなりました。

クエリの期間を変えれば様々な切り口で分析できる。

ところで、プロパティのデータを見ると、データソースにSQL文字列が入っているのがわかります。

SELECT Format([売上日], "yy 年 mmm"), Sum([売上]) AS [SumOf売上] FROM [Dash_日別売上] GROUP BY Format([売上日], "yy 年 mmm"), Year([売上日])12 + Month([売上日])-1 ORDER BY Year([売上日])12 + Month([売上日])-1

これならお尻にWHEREをつけてやれば期間の指定もできるかな？と思ったのですが、残念ながら読み込み専用ということで追記ができませんでした。

仕方がないので、参照するSQLを期間に合わせて別に作ってやることにします。方法は簡単で、最初につくったクエリの条件を変えてやるだけです。

期間の指定をするときには、以下をサンプルにつかうとよいでしょう。

前年同月の1日以降～現在まで

>=DateSerial(Year(Date())-1,Month(Date()),1)

前年同月の1日～月末

Between DateSerial(Year(Date())-1,Month(Date()),1) AND  DateAdd("m",-11,DateSerial(Year(Date()),Month(Date()),1))-1

本年1月1日～現在

>=DateSerial(Year(Date()),1,1)

当月1日～現在

>=DateSerial(Year(Date()),Month(Date()),1)

前月1日～前月末

Between DateAdd("m",-1,DateSerial(Year(Date()),Month(Date()),1)) And DateSerial(Year(Date()),Month(Date()),1)-1

前年1月1日～前年年末

Between DateSerial(Year(Date())-1,1,1) And DateSerial(Year(Date())-1,12,31)

Microsoft Accessでアプリを作成中。まだお客様の管理画面にとどまっていますが、だいぶ作りこみが進んできました。

ご予約と直来店のボタンを作りました。その名の通り、将来の日付を指定する場合と、お客様が予約なく直接来店した場合にすぐに登録できるボタンを分けてみました。

登録用のダイアログが開きます。予約日は手入力できますが、本日や現在時刻のボタンを押すとその数字も入ります。

メモ欄は空欄でも入力しても構いません。予約登録ボタンを押したときに、メモ欄が空欄だった場合は、「ご予約」という文字列が入るようにしました。もちろん後から書き換えられます。ここは環境設定画面で切り替えられるようにしておいてもいいかも。後で考えます。

登録すると画面下の来店履歴に加わります。最初の段階では「ご予約中」のステータスが表示されています。さっき入力したメモ欄はここでも直接編集できます。

美容院での担当っていう概念がいまいちわからなかったので、お客様のマスタに担当者はあるのですが、来店時にその人がいなかったらどうなるの？ってことも考えて、来店履歴ごとにも担当欄をつけています。このデータは「DAT_売上」のテーブルに書き込まれます。

さて、実際にお客様が来店したら、「明細」ボタンを押します。

最初の段階ではカテゴリが入力待ちになっています。実際に行ったメニューに切り替えていきます。

カテゴリを切り替えると、メニューも「MST_メニュー」のテーブルに従って表jいされます。そのテーブルに登録されている単価が自動反映します。複数のメニューを追加すると、売上合計も自動計算されます。

売上合計の部分は以下のようにコントロールソースにSUM関数を組み込むことで簡単に仕込むことができます。

最後に閉じるを押すと、ダイアログは閉じて来履歴一覧の金額欄に反映するべきだんだけれど、ここに苦労しました。「DAT_売上明細」テーブルではメニュー単品が売上IDと関連づいているので、表示だけSUM関数で都度計算させてもいいかな思っていたんだけれど、結局は「DAT_売上」テーブルに「売上合計」という列を作って、DAOのレコードセットをEDITを使って変更するという方法を使ってみました。

これで「ご予約中」のオレンジの表示が消えて、4000円という金額が表示されました。

ステータスを条件付き書式で色分け

色使いとか表示はもうちょっと工夫がいるかもだけれど、同じ予約中でも条件によって色分けさせました。

当日なら日付が緑になって、かつオレンジ色の「ご予約中」表示。未来日ならご予約中は茶色。過去日で結局お客様来店しなかった場合はブルー。

フォームで「書式」のリボンを選択すると、「条件付き書式」というボタンが表示されます。条件によって色を変えたいセルを選択した状態で押します。

日付が当日より大きい値で、かつステータスが「予約中」か、など柔軟に設定できます。ノーショー（現れなかった）場合は背景をグレーにするなどもっとばっさりわかるようにしてもよいかもですね。

ノーショーが多い要注意客をさがせる？

ノーショーは「DAT_売上明細」を登録しなかったものなので、カテゴリが「入力待ち（ID999）」のものをクエリで絞り込めそうです。そういう切り口で分析するのもアリかもしれませんね。