モカ式

MSAccessでアプリ作ってます。簡単にできて効果が高い、フォームのタイトル部分をクリックすると昇順、降順の並び替えが簡単にできるコードを自分のためにメモ。

つまり、こういうやつです。

上の図はふりがな順になってますが、たとえば「お客様ID」のところをクリックすると、順番がソートされます。

お客様IDの昇順になりました。もういちどクリックすると降順になります。こういうのはあると便利です。

じつは上のように同じAccessのフォームでも「データシート」形式を使えばあらかじめこのソートや検索機能はついてきます。けれど、ボタンを設置したり画像を表示したりができないので、複数のアイテム形式でもソート機能をつけるのが今回のテーマです。

タイトル部分はラベルでできています。まず、ここをクリックしたときにFunctionを動かす部分はこんな感じです。

Private Sub lbl_ふりがな_Click()
    sortKey = "ふりがな"
    labelSort (sortKey)
End Sub

sortキーはどのラベルをソートさせるのか区別するためにつけている変数です。labelsortファンクションに渡します。

Function labelSort(sortKey)
    If Me.Form.OrderBy <> sortKey Or Me.Form.OrderBy = sortKey & " DESC" Then
        With Me.Form
        .OrderBy = sortKey
        .OrderByOn = True
        End With
    Else
        With Me.Form
        .OrderBy = sortKey & " DESC"
        .OrderByOn = True
        End With
    End If
End Function

本体はこんな感じです。

ここでラベルにつけていた変数が生きてきます。他のラベルにもソート機能をつけるには、クリックしたときにそのフィールドの名前をFunctionに渡してやるだけなので、ずらずらと以下のように機能を並べていきます。

Private Sub lbl_お客様ID_Click()
    sortKey = "お客様ID"
    labelSort (sortKey)
End Sub


Private Sub lbl_ふりがな_Click()
    sortKey = "ふりがな"
    labelSort (sortKey)
End Sub

Private Sub lbl_過去１年回数_Click()
    sortKey = "過去１年回数"
    labelSort (sortKey)
End Sub

Private Sub lbl_最終来店日_Click()
    sortKey = "最終来店日"
    labelSort (sortKey)
End Sub

Private Sub lbl_氏名_Click()
    sortKey = "氏名"
    labelSort (sortKey)
End Sub

Private Sub lbl_担当者_Click()
    sortKey = "担当者名"
    labelSort (sortKey)
End Sub

レポート機能のグラフ作成がわかってくるといろいろ作りたくなります。美容院向けCRMに売上昨対、客数昨対の推移を表すグラフをつけてみました。

これを作りるには、月ごとの売上と前年の売上を一つのレコードに並べたクエリを作らないといけないのですが、これがなかなか難し。いろいろなやり方があるのでしょうが、簡単でしっくり来た方法を以下のサイトから参考にさせていただきました。

「Access実践サンプル」http://psp8155.blog13.fc2.com/blog-entry-194.html

管理者のむさしさんも書いてらっしゃいますが、エクセルでは簡単にできることが、意外と難しいのです。ただ前年の数字を横に並べたいだけなのに。

例に従って、まずは二つのクエリを作ります。

クエリ１をつくる

一つ目のクエリには昨対比１と名付けました。売上の合計を「本年売上」、売上のカウントを「本年客数」としました。一番右の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年間で同一になってしまうので、年にも「当年」のようなラベルをつけて、表示月:[当年]&[当月]のように年度が別行になるように工夫すると良いと思います。

前回の応用で日別のカテゴリ別売上合計のクエリをつくることで、商品売り上げ構成の円グラフを作ってみました。

見たまんまですが、この元になるクエリは以下のような感じ。数があったほうが楽しいので、小さいほうの円、前月のほうのクエリです。

設計はこんな感じになっています。画像が小さくなったので拡大してみてくださいね。クエリの段階でカテゴリごとの合計売上を出してもよいのだけれど、グラフ化するときに自動で合算してくれるし、この状態のほうが出現した数も数えられるので（たとえばカラーは2件あります）シンプルです。

DAT_売上詳細には「売上」という項目がありません。カテゴリメニューの「単価」×「数量」－「割引金額」で売上を出しています。3列目にある　売上:[単価]*[数量]-[割引金額] のところ。こうしておくと結果には先頭の「売上」という名前がつきます。あまり多様するとクエリが重くなるのだけれど、使い方次第で便利です。

あとはレポートでグラフを追加、クエリを指定して円グラフを選んで、適当に選択すると円グラフの出来上がり。

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)

Accessで美容院向けCRMを作っているのですが、ママ友のお仕事関係で鍼灸院さんでベータテストしてもらえることになりました。お渡ししたのはVer0.1。ちょうどコロナウイルス騒ぎでお客様も迎えられない時期なので、お客様カルテとして使っていただけているようです。

また、カテゴリや従業員マスタの管理もできないといけないので、専用画面を作り、誤って操作してデータベースを壊してしまわないようにしないといけません。ということで、簡単なパスワードでマスター管理画面を開けるようにしました。

マスタ編集というタブを付けました。ここをクリックすると、管理者パスワードを求めるようにしました。「0000」を入力すると、グレーアウトしているボタンが利用できるようになります。

ボタンのグレーアウトと解除

タブのクリックというアクションでサブフォーム上のボタンを制御するのが意外と難しかったので自分へのメモ。長い・・・

じつは、このタブの部分を押して表示されるInputBoxでは日本語入力になってしまうので、それを切りたかったんだけれどわからず。IMEの制御を指定したテキストボックスを作り、そこにフォーカスを当ててからInputBoxを呼び出すと、IME制御がつられることがわかりました。ただし、そのテキストボックスにフォーカスを当てる前に、サブフォームにフォーカスを当てるのがコツ。

機能追加はオプションで切り替え

ベータテストをしていただいて、さっそく機能追加のリクエストをいただきました。

• 売上メモについて、ビフォー、アフター、ネクストプランの枠で作ってほしい
• 誕生月だけでなく、誕生日も管理できるようにしてほしい

機能は盛り込んでいきたいのですが、シンプルで使いやすいこととも両立させたいので、昨日追加はオプション設定画面で使うか使わないかを切り替えられるようにしようと思います。

使いたい機能にチェックをいれるとフォームの一部がかわるという仕組み。でも機能追加ではデータベースにフィールドを加えないといけないケースが出てきます。今回の要望はまさにそのケース。でも、すでに追加してるお客様のデータを消してDBを差し替えていただくわけにもいかないので、DB更新のボタンを付けました。アップデートの時にはこれを押して、DBに列を追加しようというわけ。SQLのALTER命令を使います。ただいまテスト中。多分以下のようなので行けるはず。

ご意見・ご要望フォーム

マスタ編集画面に、Googleフォームでつくったアンケートのようなものが開くようにリンクを付けました。

カテゴリー編集画面

データベースになじみのない人には難しいかもしれないので、なるべくわかりやすく、カテゴリとアイテムを横に並べて表示させるようにしました。

担当者マスタ編集画面

担当者マスタはシンプルです。将来的にはパスワードや権限を加えて、店内メール機能みたいなのを加えようと思っています。

お客様一覧編集画面

この画面はあまり自由に触ってしまえるとDBを壊しかねないので、昨日として残すかどうか迷いどころ。お客様単位でなくてリストの状態で編集できるのは、楽かもしれないんだけれど、どうしようかな。

人に使ってもらうものを作るのって難しい。

これまでは、自分の中のイメージだけで作ってきたのだけれど、いざ人に使ってもらうとなると、いろいろ考えないといけないことが多いことに気が付かされます。例えば、データベースは一度入力をし始めてしまうと簡単にコラムを加えることもできません。初期データをいれるのに、CSVの取り込みなどもできたほうがよかったでしょう。このあたりは自分でやるならば直接テーブルへ流し込めばよいのですが、すでに誰かのPCに入ってしまっているものをたやすくいじるわけにもいきません。

機能追加も同様で、今後の拡張性やほかの機能との整合性も保ちながら作るって難しいところですね。