時間を10分きざみに丸めるVBA。Accessで使えるCeiling関数を使わない方法。

先日、美容院用顧客管理システムで予約時間をボタンひとつで、10分単位の時間で入力されるようにしました。この時、参照設定でExcel関数を読み込み、ExcelではよくつかわれるCeiling関数をつかったわけですが・・・。

作ったアプリをベータテストしてくれているアロマサロンさんで試してもらったところ思わぬ落とし穴。AccessRuntimeで動作させているPCにExcelがインストールされていなかったのです。

Excel関数つかえないのでINT関数を代わりに使う。

というわけで、代替案。INTを使います。INTはご存じの通り引数に小数が入ると、整数を返します。日付型は24時間を1と表し、時刻は小数として表します。試しにエクセルで表示させてみました。

見ての通り、0:00はゼロ。10分加えるごとに0.0069444444となります。この数字は、ほぼ1/144(144分の1)です。同じ数で割ると1になります。(ほぼ)

こんどは0:10から1分ごとの変化です。1/144で割った値を比べると、1分増えるごとに0.1増えていることがわかります。0:20になった時点で2に繰り上がっています。

INT関数は小数点以下を切り捨てて整数にしますから、0.1ずつ増える部分は全部無視されて、10分刻みの整数だけが残りました。

ひとつ前の画像のように割った数字をもう一度かけると元に戻ります。1分単位の部分は切り捨てているので、もちろん1分毎で変化はありません。これをコードで表すと以下の通り。

Private Function intまるめ(t)
    intまるめ = (Int(t / (1 / 144)) * (1 / 144)) 
End Function

このままだと切捨てなので、切り上げにするには10分を表す1/144を足してみました。もうお気づきでしょうが、大体大丈夫なのですが、0分ちょうどだと、10分進みます。うーん。

まあいっか

Private Sub btn_excel_Click()
    Me.txt_marume = excelまるめ(Me.txt_moto)
End Sub

Private Sub btn_intmarume_Click()
    Me.txt_marume = intまるめ(Me.txt_moto)
End Sub

Private Function excelまるめ(t)
     excelまるめ = Excel.Application.Ceiling(t, 1 / 144)
End Function

Private Function intまるめ(t)
    intまるめ = (Int(t / (1 / 144)) * (1 / 144)) + (1 / 144)
End Function

今回の用途の場合、0分単位で10分進んでしまうことは特に大きな問題でなかったのでこのまま使っちゃいました。Accessで以下のように簡単なテストを作って比べてみると、やはり10分単位ぴったりの時は差がでますがご愛敬。というわけで、いま焦って脱Excel参照設定を進めてます。ENCODEURL関数も使っちゃっているので、道のりは長い・・。

誤差はいろいろ出てきそうな方法ですが、アバウトな用途でどうぞ。

MSAccessでボタンで入力する現在時刻を10分単位でまるめてすっきりするVBA。

ご予約登録画面で時間の入力を簡単にするためにボタンをつけるってことはあると思うのですが、単純に現在時刻だとどうにも不格好です。そこで、時刻をまるめられないかと探していたらこちらのサイトで方法が紹介されていました。

Excelでお仕事! http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_050_030.html

エクセルのFloor関数で切り下げ、Ceiling関数で切り下げ。その際に10分刻みなら10分=1/144日、15分=1/96日 というのを利用すれば実現できそうです。

AccessでもExcelの関数を参照設定でExcelのオブジェクトライブラリを選択すれば使えます。早速以下のようにしてました。まるめFuncitonで10分単位の切り上げが実現しました。

Private Sub btn_from1hourAfter_Click()
    Me.予約時刻 = まるめ(DateAdd("h", 1, Time()))
End Sub

Private Sub btn_from2hourAfter_Click()
    Me.予約時刻 = まるめ(DateAdd("h", 2, Time()))
End Sub

Private Sub btn_FromNow_Click()
    Me.予約時刻 = まるめ(Time())
End Sub

Private Function まるめ(t)
    まるめ = Excel.Application.Ceiling(t, 1 / 144)
End Function

1/144のところはコンボボックスなどで差し替えれば、15分単位などにもできますし、切り上げ、切り下げをどちらにするかもオプション画面つくってユーザーが選択できるようにしてもよいかもしれません。

Accessで指定したURLを既定のブラウザで開くVBA。

AccessはIEを使ってWEBページを開くことは簡単にできますが、Chromeなど指定したブラウザで開くのにいい方法がおもいつかず。ChromeのPATHを書いて引数で開くなどしていましたが、画期的な数行のコードをみつけたので自分のメモとして書いておきます。

紹介してくれたのはこちらのサイトです。

513号室 http://bardiel-of-may.blogspot.com/2012/06/url.html

Sub testOpenUrl()
    Dim objWSH As Object
    Dim URL As String
    
    URL = "https://mocabrown.com/blog"
    
    Set objWSH = CreateObject("WScript.Shell")
    objWSH.Run URL, 1
End Sub

.Run “notepad.exe” のような感じでアプリを起動できるのは知っていたが、URLを渡すと既定のブラウザで開くってのは魔法のよう。情報ありがとうございます。

タイトル部分をクリックで簡単ソートする方法。開発18日目

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_過去1年回数_Click()
    sortKey = "過去1年回数"
    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

MSAccessからWindows10標準のメールを使う。URLエンコードに苦労したけどExcel関数が使えるの巻。開発17日目

美容院向け顧客管理システムをお休みを使って作っています。ベータテストをしていただいている方から、「前日にお客様にご予約確認のメールを送りたい」とリクエストをいただいたので、実装中。MSAccessでメールを扱うのは初めての経験だったので、いろいろ学びがありました。

Accessでメールを送るには主に3種類の方法あり

1.Outlookオブジェクトを使う

Office製品であるOutlookと連動させる方法で、参照設定の「Microsoft Outlook 16.0 Object Libraryを使います。オブジェクトをAccessで作って使う方法で多分これが正攻法です。

こちらのサイトで非常にわかりやすいサンプルコードを紹介してくださっています。税理士が教えるAccessとExcelで経理会計の仕事を効率的にする方法

難点はOutlookを普段メールクライアントとして使っていないといけないこと。

2.Access単体でメールを送れるDLLを導入する

メールクライアントへ情報をコピーするのではなくて、Access自体にメール機能を組み込むためのDLLはいろいろ存在するようです。「Access VBA メール DLL」のようなキーワードでググるといろいろ出てきます。

参照設定で使えるMicrosoft謹製のものもあるようで、Microsoft CDO for Windows 2000 Library というのがそれみたい。2000か!!サンプルコードは下記のサイトで紹介されていました。

これは面白いんだけれど、今回はそんなに件数がない美容院のご予約お知らせメールのようなものなので、定型文を確認しながら送ってほしいなというところもあるのでパス。大量のご送信を起こしそうで自分の技量に自信がないのが主たる理由。

でも、これが使えると、従業員向けに「本日ご予約のお客様一覧」的なものを携帯へ送ってお知らせ、なんて使い方はできそう。

3.古のキーワード mailto: をつかう

いわゆるホームページを作ったことがある人ならご存じでしょ? ハイパーリンクに hoge@hoge.com のようなリンクを設定しておくと、クリックしたときに自動でメールアプリが起動して宛先が設定されるのです。

mailto:  には、じつは宛先だけではなく、件名や本文も潜り込ませられます。一続きの長い文字列にするだけでオッケーです。しかもメールクライアントは使う必要がありますが、Outlookでもユードラでも手裏剣でも、もちろんWindows10標準のメールでも規定のメーラーを起動させるので汎用性があります。君に決めた!

そして地獄が始まる。エンコードってそういうことか

なんだ楽勝じゃん!とAccessでテストのフォームを作り、ラベルに以下のようなハイパーリンクを設定してみました。

mailto:hoge@hoge.com&subject=こんにちは&body=これはTEST本文です

宛先やタイトルは入るのに本文がなぜか反映せず。クライアントはWindows10標準のメールです。

どうやら日本語の部分をエンコードする必要あり。よくURLに日本語が含まれているときにURLをコピーしてテキストに貼り付けると%がついた長い英数が並ぶことがありますが、あれは文字がエンコードされたもの。とくにWEBなどで使うエンコードはURLエンコードやパーセントエンコーディングと呼ぶようです。

ならばVBAでしてみようではないか。パーセントエンコーディングというものを!

ググります。「VBA パーセントエンコーディング」

なるほど、Scripting Controlというのを使えばよいらしい。サンプルコードを借りてきてテストしてみると、なぜかエラー・・・。

ならば参照設定からせててみると・・・

やはり黄色いところではじかれます。なんで? ふたたびググります。「createobject(“scriptcontrol”) 使えない」

答えはこちらにありました。https://teratail.com/questions/10072

このスクリプティングコントロールは、JavaScriptのコードを解釈、実行するもののよう。WEBで使われる技術なら納得。でも実は64bit環境のVBAではサポートされていないよう。解決法は、「32bitのAccessを入れる・・・」おいおい。

ググったらExcelの情報はたくさん出てくる。おや?まてよ?

URLエンコーディングをググるとExcelの情報は非常にたくさん出てきます。なんと、Excel2013以降では標準関数として「ENCODEURL()」という関数を持っていて、引数に入れるだけでパーセントエンコーディングを吐き出してくれるという神のような存在。

ならばAccessに参照設定でExcelのオブジェクトライブラリを取り込んでやればいいんでは?

そして編み出した究極の変換コードは・・・

Private Sub コマンド36_Click()
    enc_本文 = Excel.Application.encodeurl(txt_本文)
    enc_件名 = Excel.Application.encodeurl(txt_件名)
    str_mailto = "mailto:" & txt_宛先 & "?subject=" & enc_件名 & "&body=" & enc_本文
    Me.HL.HyperlinkAddress = str_mailto
End Sub

なんともあっけなかった。Excel.Application.encodeurl(“変換する文字列”) だけで解決しました。MSAccessのVBAでエンコーディングするためにどれだけの時間を費やしたことでしょう・・・。

追記:なんやかんやでメール画面ができました。

クリックで拡大

というわけで、あらかじめ用意したヘッダーとフッター、その間に明日以降のお客様のご予約日時を差し込む形のメールを、「セット」ボタン1つクリックで左側のプレビューに表示させ、「メールアプリへ転送」というボタンにリンクづけられたMailto:で既定のメールアプリへ飛ばすという仕組みができました。

ボタンを押すと右側の予約一覧の「確認済日時」にボタンを押した日時を入力。目立つように条件付き書式でオレンジになります。DAOで売上IDがレコードになければ追加。あれば更新という条件分けさせています。

Private Sub btn_送信_Click()
        Dim DB As DAO.Database
        Dim RS As DAO.Recordset
        
        Set DB = CurrentDb
        Set RS = DB.OpenRecordset("DAT_コミュニケーション", dbOpenDynaset)
        
        RS.FindFirst "売上ID = " & Me.txt_売上ID
  
        If RS.NoMatch = False Then
            RS.Edit
        Else
            RS.AddNew
            RS!売上ID = Me.txt_売上ID
        End If
        
        RS!カテゴリ = "メール通知済"
        RS!日付 = Now()
        RS!内容 = Me.txt_本文
        RS!お客様ID = Me.txt_お客様ID
        
        RS.Update
        RS.Close: Set RS = Nothing
        DB.Close: Set DB = Nothing
        
    Me.subform.Requery
End Sub

「セット」を押してもメールアドレス登録していないお客様は「メールアドレスを登録されていないお客様です」と警告だしたり、チェックボックスでリストに表示させないようにしてます。これもフィルターをつかって以下のようにしてます。

Private Sub chk_noMail_AfterUpdate()
    Me.Filter = "Email"
    If Me.chk_noMail Then
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
End Sub

まとめ

Excelの便利な関数はAccessでも使うと便利。

そういえば、最近はエクセルに株価データや地理データ、日本のGDPなんかもWEBから読み込んでくる動的な関数が追加されたようですね。https://dekiru.net/article/19825/

こういうのも組み込んでアプリ作れちゃうんじゃないの?と期待が膨らみます。

Accessで昨対比を作ってグラフ化する。開発16日目。

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

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

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

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

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

クエリ1をつくる

一つ目のクエリには昨対比1と名付けました。売上の合計を「本年売上」、売上のカウントを「本年客数」としました。一番右のWhere条件は売上が0円のものは除外するために0より大きいことを条件にしてあります。

クリックで拡大

クエリ2をつくる

二つ目のクエリには昨対比2と名付けました。上の結果表示との違いは連結列だけです。昨対比2のクエリのほうが1年進んでいます。これがズレをうまく作ってくれます。昨対比2では売上と客数のタイトル前に前年とつけているのもポイントです。

クリックで拡大

これが本命3つ目のクエリをつくる

最後に実際にグラフ表示につかう三つ目のクエリを作ります。クエリの名前は数字なしで「昨対比」このクエリが昨対比前年比を実現するための肝です。

クリックで拡大
クリックで拡大

小さくなってしまったので、拡大してみてください。見ての通りポイントは二つのクエリを3つ目のクエリで連結という項目をリレーションで結んでいるところです。むさしさんがおっしゃる通り、前年に数字がない場合に備えて片側向きのリレーションにしてあります。

これさえできていれば、あとはクエリの中で「本年÷前年」してやれば昨対比の出来上がり。グラフでパーセント表示させる際に不格好にならないようにRound関数を使って少数点2桁に丸めています。

最後の「表示月」というのはグラフのX軸に「〇月」と表示させるためのひと手間です。2列目の月をわざわざ「当月」に名前を変えて使っているのは、「月」のままだと二つのクエリのどちらを指定しているかわからないと拒否られたため。

あと、細かいことですが、「年」の抽出条件に 「Year(date())」と入れてて置くことで、1月から12か月間、営業日が進むにつれてグラフが成長する感じになります。

あとはグラフの設定で軸(項目)に「表示月」を選び、軸(Y軸)に「売上昨対(なし)」および「客数昨対(なし)」の2か所にチェックを入れてやります。

Accessのレポート機能にあるグラフ作成についてもう少し詳しく知りたい方は前回(開発14日目、15日目)の投稿を参考にしてくださいね。

2年分表示させたければもう一つ「Year(date())-1」をOR条件として付けくわえるのですが、表示月が2年間で同一になってしまうので、年にも「当年」のようなラベルをつけて、表示月:[当年]&[当月]のように年度が別行になるように工夫すると良いと思います。

商品カテゴリ別の割合円グラフをつくってみたよ。開発15日目

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

クリックで拡大

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

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

クリックして拡大

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

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

レポートで売上グラフなどダッシュボードをつくる。美容室向け顧客管理システム 開発日記14日目

ダッシュボード機能

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

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

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

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

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

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

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

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

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

クリックで拡大

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

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

クリックで拡大

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

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

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

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

グラフのタイトルなどは「プロパティシート」で編集します。「グラフのタイトル」という項目をかえてやると、グラフのタイトルが変化します。凡例は今回のグラフでは要素が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のInputBoxのIMEをOFFにするオプション画面を作ってみた。開発13日目。

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

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

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

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

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

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

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

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

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

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

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

ご意見・ご要望フォーム

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

カテゴリー編集画面

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

担当者マスタ編集画面

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

お客様一覧編集画面

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

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

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

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

Accessのフォームで写真をクリックすると大きく表示させる。開発12日目。

MSAccessで日曜開発してる美容院向けCRM。だいぶ形になってきました。お客様の写真を表示できるようにしました。クリックすると写真を大きく表示させたい。思いつくのはクリックのアクションでファイル名取得してビューアーに渡して開く、なんですけれど、リンクをつかった簡単な方法で解決しましたので紹介します。

まず、写真の表示はこのようになっています。イメージオブジェクトを写真を表示させたい場所に配置し、そのコントロールソースに写真のパスとファイル名を組み込むだけです。ピクチャタイプは「リンク」です。

「ご予約」ボタンの下にあるテキストボックスは非常時ですが、ここにが[写真FileName]になってます。Dlookupで取得しているのは、ローカル設定テーブルに記録した写真を保存するフォルダのパスです。

写真をWindowsの規定のビューアで表示する

Accessの開設をしているサイトをいろいろ探したのですが、意外とこの方法は紹介されていませんでした。常識だからなのか、知られてないのか?まさかね。

ともかく方法は簡単で、書式の「ハイパーリンクアドレス」の項目に、さっきのコントロールと同じファイルのパスとファイル名が入るようにしてやればいいだけです。

クリックすると、写真ビューアが起動して開いてくれました。この方法だとGifやPNG,JPGなど拡張子を気にする必要なくファイル名を渡してくれるのでラクチンです。

Dmaxに複数条件をつけるコツはAndでつないだ文字列を意識すること。美容室向け顧客管理システム 開発日記11日目

Accessでアプリを作っています。昨日はDmax関数をつかって、お客様の最終来店日を表示できるようにしましたが、なんだか変なことに気が付きました。

最終来店日を表示させる目的は、その名のとおり前にいつお店に来てくれたかを把握することです。美容院ならば前回からどのくらい経過しているかって重要な情報の一つですよね。

最終来店日は「DAT_売上」テーブルの「売上日」の一番大きい数字(最近の日付)を条件に沿って取得するものです。前日はクエリにこんな感じに関数をせっていしました。

でも、これだと予約登録を入れたときに、「DAT_売上」テーブルの最新日付が当日に更新されてしまうので、せっかくの最終更新日が今日の日付になってしまいます。

DmaxはクエリのFrom句に指定していないテーブルに条件設定をできる便利な関数ですが、上記の例では条件を1つしか設定していません。複数の条件をDmax関数に設定できないのでしょうか?ヒントはYahoo!知恵袋にありました。

Dmaxは3番目の引数に複数の条件をAnd句でつなげることで複数条件指定することができました。ただし、このYahoo!知恵袋にもあるように条件の書き方のポイントをつかんでいないと変数が大混雑してうまくいきません。

Dmax関数のポイントは、3つの引数をすべて””で囲んで文字列とすることでした。つまり、3番目の条件に当たる部分も And区切りの文字列になるよう意識しないといけないのです。

そこで、条件を

  1. お客様IDが、今表示されているお客様のIDと一致すること
  2. 売上日が、明日以降であること

にしてやると、Dmax関数は以下のようになります。

やたらと”で区切りながら&でつないでいるので難しいように見えます。「&」は文字と変数をつないでいて、アルファベットの「And」は条件と条件をつなぐ句です。カンマは2つあります。つまり関数の引数は3つです。3つめは”お客様ID~ 始まるので、この部分だけを実際の文字列に直すと以下のようになります。

こうしてみるととてもシンプルです。先に手書きで仮の条件を書いてから「” & 変数 & ” 」に置き換えていくほうがわかりやすいかもしれません。

3つ目の条件も加えてみる

「DAT_売上」マスターには、NoShowというBooleanのフラグを設定していました。つまり、予約はしたけれど、結局来店しなかったレコードにつけるフラグです。来店していないので、このレコードは最終来店日の検索からは除く必要があるでしょう。つまり、条件は以下のとおりになります。

  1. お客様IDが、今表示されているお客様のIDと一致すること
  2. 売上日が、明日以降であること
  3. NoShowフラグはFalseであること

以上の3つの条件をもとにDmax関数をつくると以下のようになります。

今日の予約と前回のNoShowは無視された

上記のDmax関数をクエリに組み込んだ結果は上のようになりました。今日は3月25日です。24日に予約がされましたが、来店されなかったのでNoShowにチェックが入っています。その前は3月13日に来店して武将カットで売上が上がっています。

お客様一覧を開いてみると、最終来店日はちゃんと3月13日になっています。実際に前回秀吉さんにカットを行った日付です。これで役に立つ情報になりました。

Dmax関数で他のテーブル条件を加えてクエリに入れ込む。美容室向け顧客管理システム 開発日記10日目

MSAccessでアプリづくり。今日はお客様一覧に最終来店日をつけてみました。

「MST_お客様」テーブルに最終来店日の項目をつくってはいたのですが、お客様一覧に表示させるだけなら、ついでにクエリで最新の売上日を出せばよいだけかも?ということで、クエリをいじりました。

このお客様一覧は、「Q_お客様一覧」というクエリで「MST_お客様」というテーブルを整理して表示させているだけです。一方、最終来店日は「DAT_売上」のテーブルから計算します。つまりテーブルを横断します。

そこで作ったクエリですが、上部にはお客様と担当者のテーブルはありますが、売上に関するテーブルが表示されていません。

ポイントはクエリの左から三番目でつかっているDmax関数です。

とてもシンプルな関数ですが、2番目の項目に””でくくっているのはテーブル名です。意味は以下のような内容になります。

「”DAT_売上”テーブルの、”売上日”項目の最大値(日付も見かけは/区切りですが、実際には大きな桁の数字で管理されているので数字です)を表示させる。その際の条件は”DAT_売上テーブル内のお客様ID項目 =  このクエリで使われている「お客様ID」”と一致するもの」

とりあえずこれだけでほかのテーブルから、今のクエリで使っている項目を条件にして最大値である日付、つまり最終来店日を表示させることができます。

弱点は今日お客様が来店した際に今日の日付を出してしまうとか、予約はしたけれども実際には来店しなかったとか・・・。そういう場合にも日付を表示してしまいます。

「DAT_売上」テーブルにはNoShowのフラグを作っているので、そのうちこちらを組み合わせてNoShow(来店しなかった)を除外する工夫も作りこもうとおおもいますが、今日のところはここまで。