中間テーブル多対多の関係をつくる。美容室向け顧客管理システム 開発日記5日目
Microsoft Accessで開発始めてみました。せっかくお客様管理するので、お客様同士の関係を見える化したら面白いと思いました。
紫式部さんのお客様情報詳細画面をひらくと、右下のお友達リストに小野小町さんが表意jされています。関係もメモで記録しておくことができます。
この関係性は「TBL_お客様関係」のテーブルで管理しています。
IDは関係性ごとにつけているキーで連番です。お客様IDの3は小野小町を表し、お友達IDの4は紫式部を表します。
このような中間テーブルをつかうと、関係性を多対多で表現できます。
3番の小野小町さんを開いた状態で、そのお友達IDを抽出しようとすると、こんな感じのクエリになります。
結果はこんな感じ
ここまでは簡単なんだけれど、このテーブルを単純に使うと、小野小町さんの画面に紫式部さんはお友達として表示されても、紫式部さんのところには小野小町さんは表示されないのです。
ではこんどは、4番の紫式部さんの画面を開いた状態で、以下のようなクエリをつかって、紫式部さんをお友達として登録しているお客様IDを抽出するクエリをつくります。
お互いに表示されるようになりました。
問題はこの二つの条件を同時に実行するクエリが必要。こんな時につかうのがユニオンクエリです。
先ほど作った２つのクエリをSQLで表示させて、あいだをUNIONでつなぐだけ。「;」は取り除きます。
すごく複雑に見えますが、UNION を真ん中において前後にふたつのSQLをコピーしただけです。
この辺の作り方もT’S Wareさんの記事を参考にさせていただきました。
ぶじにお互いのお友達リストに表示できるようになりました。クエリには中間テーブルである「TBL_お友達関係」のIDも含めています。メモ欄を直接あとで編集できればよいのだけれど、どうもUNIONを使っているクエリを参照したフォームは直接編集できないようなので、メモ欄をクリックしたときに編集用のダイアログが開くようにしました。この時に使っているのは中間テーブルのIDです。
Private Sub 関係性メモ_Click()
DoCmd.OpenForm "F_関係性メモ編集ダイアログ", acNormal, , , , , [ID]
End Sub
7番目の[ID]は中間テーブルのIDです。OpenArgsとしてこれから開くフォームに渡す値です。
このダイアログが開くときに、OpenArgsで渡された中間テーブルのIDからメモ欄に表示させます。変数でもよいんだけれど、lbl_IDというラベルを作って、そこにOpenArgsから読み込んで使ってます。
メモ欄への表示には1行で処理できるDlookupを使いました。
Private Sub Form_Open(Cancel As Integer)
lbl_ID.Caption = Me.OpenArgs
Me.txt_関係性メモ = DLookup("関係性メモ", "TBL_お客様関係", "ID = " & lbl_ID.Caption)
End SubOKボタンをおすことで登録。こっちにはDAOをつかいました。
Private Sub コマンド1_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("TBL_お客様関係", dbOpenDynaset)
RS.FindFirst "ID= " & Me.lbl_ID.Caption
RS.Edit
RS!関係性メモ = Me.txt_関係性メモ
RS.Update
RS.Close: Set RS = Nothing
DB.Close: Set DB = Nothing
Me.txt_関係性メモ.Value = ""
Me.lbl_ID.Caption = 0
DoCmd.Close acForm, Me.Name
End Sub
お友達IDをクリックでお友達を表示
お友達の間をどんどん移動できるように、お友達IDの数字の部分をおすことで、相手の詳細画面が開けるようにしました。これはフォームをDoCmd.OpenFormで開くだけ。4番目の引数がフィルタなのでお客様IDで絞り込んでいます。
Private Sub お友達ID_Click()
DoCmd.OpenForm "F_お客様情報詳細", acNormal, , "ID=" & [お友達ID]
Me.Refresh
End Sub