|
||
| <HOME <お願い事項 <Access2000 TOP <Access97 TOP <サイト内検索 | |
|
|
||
| MS-Access2002 なりきりデータベース設計 | ||
| 1 2 3 4 | ||
■パート１、極力テーブルに「無駄」を設けずに、クエリや関数などを駆使して値段や売上金額を出す方法
まず、こういうテーブル構成に変更します。
前のページのサンプルDBをお使いいただいてる場合は、ちょっとわかりにくいかもしれません。。。。
とりあえず話だけでも聞いてください。
さらに、「商品マスター」テーブルの構造をこんな↓風に変えました。
「値段」フィールドを削除します。別に残しておいてもいいんですけど、紛らわしいですからね。。。。
で、↓こんなテーブルを新たに作ります。
名前を、「値段変動テーブル」にしました。もうちょっとわかりやすい名前のほうがよかったかも・・・。
まあいいや。
このテーブルの内容はこんな感じです。商品それぞれ、いつ、どういう値段に変わったのか・・・という履歴が残るテーブルです。
このテーブルの中の、「変更日が一番新しい（一番大きい）」ものが、今の値段である・・・という判断をそこここで行うわけです。
で、このテーブルを基に、こういうフォームを作ってみました。
基本的に「新規入力（新価格を入力するときだけ開くフォーム）」ということで、フォームのプロパティ「データ入力用」を はい にします。
そうすると、新規入力だけを受け付けるフォームになりますもんね。
で、商品番号を「コンボボックス」にして、一覧から「値段の変更を行う商品」を選択できるようにしてみました。
変更日は、今日の日付が自動的に入るように、テキストボックスの「既定値」プロパティを使ってみましたが、自分でちゃんと入力するようにしておいたほうがいいのかもしれませんが・・・。こういうのは、実際に使ってみないとなんともいえないですね。
もし、「今の値段」と「その値段に変更した日（つまり最終更新日）」を表示させたい場合は、そうだな・・・コンボの下に非連結のテキストボックスをふたつ作って（非連結のテキストボックスってどうやって作るの？と思った人、いないですよね？大丈夫ですね？）、コンボボックスの「更新後処理」イベントで、↓こういうコードを書いてやればいけますよ。
Private Sub 商品番号_AfterUpdate()
Me!テキスト8 = DLookup("値段", "値段変動テーブル", "商品番号='" & Me!商品番号 & "' and 変更日 = #" & DMax("変更日", "値段変動テーブル", "商品番号='" & Me!商品番号 & "'") & "#")
Me!テキスト10 = DLookup("変更日", "値段変動テーブル", "商品番号='" & Me!商品番号 & "' and 変更日 = #" & DMax("変更日", "値段変動テーブル", "商品番号='" & Me!商品番号 & "'") & "#")
DoCmd.GoToControl "値段"
End Sub
（それぞれのテキストボックスをテキスト8、テキスト10とした場合）
それにしてもすごい・・・長い式です。
DlookupとDMaxの応酬。書き方は一通りではないので、もっと端的な書き方ができるかもしれませんが、とにかく「値段変動テーブルの中から、今フォームに表示している商品番号のデータの中で変更日が最も新しい（大きい）データの値段を、テキストボックスに表示して」という命令文にする必要があるわけです。
これは多分、相当「関数の書き方」「条件式の書き方」に慣れておかないとエラーでまくり状態になってしまうかもしれません。
慣れてない方は、やめといたほうがいいかもしれないですね・・・。
ま、まあ、とにかくこれで、「今の価格」と「最終変更日」が出ます。
このフォームに適当な名前をつけて保存をしておきます。
名前は・・・そうだな。「値段の変更」とでもしておきますかね。
商品登録の画面もちゃんとしないとならないですね。
もう、「商品マスター」からフィールドを削除しちゃったので、コントロールソースがないよ、という意味で#Name? となっちゃってますね。
ここから、新しく商品を登録した場合や値段を変更した場合、さっき作った「値段変更テーブル」にレコードが追加されていかないとならないわけなんです。ここんとこが工夫のしどころなんですが・・・。
追加クエリを使ったり、VBAでデータベースに接続してレコードセットにAddnewするやり方が通常ですが、こういう場合私はよく「フォームを埋め込んでしまう」方法を使います。仕組みさえ理解できればお手軽だと思うんで、ちょっとご紹介しますね。
まず、「値段」のテキストボックスのコントロールソース欄を↑空っぽにして、非連結のテキストボックスにします。
テキストボックスの名前は「値段」のままです。ここのところごっちゃにしないように注意してくださいね。
ツールボックスから、「サブフォーム/サブレポート」というボタンをクリックして、フォーム内をクリックし、「埋め込みオブジェクト」を作ります。
私のは、「埋め込み15」という名前になりました。
こいつのプロパティで、「ソースオブジェクト」欄に、さっき作った、値段の変更をするときのフォーム（値段の変更）を選びます。
そうすると、今開いているフォームと、値段の変更フォームそれぞれが基にしているテーブルに「主キー」がある関係で、勝手に「リンク子/親フィールド」欄になんかフィールド名が入ってしまいます。
こいつを↓削除しておいてください。必ず。
埋め込みましたが、ふたつのフォームは連動させませんので。
このフォームでは、商品番号を「商品名を入力した後に」自動的に振るようにしているので、その後、
Me!埋め込み15.Form!商品番号 = Me!商品番号
Me!埋め込み15.Form!変更日 = Date
という具合に値の代入をやっとけばOKでしょう。
登録日欄にはDate関数を使って自動的に値が入るようにしてるんですが、後で手直しできるようにしてある場合は、「登録日」のテキストボックスの更新後処理でも
Me!埋め込み15.Form!値段 = Me!値段
とやっとけば確実でしょう。
んではテストを・・・。
「次の入力」ボタンをクリックして、商品の新規登録をします。
なんか適当な商品を登録して・・・。
商品名、値段、仕入先などの基本情報を入力して、フォームを閉じましょう。
テーブルを開いて見てみると・・・。
両方のテーブルに新しい商品の情報が入ってきますよね。
テーブルの中に値が代入されていることが確認できたら、、商品登録のフォームのほうをもう一度開いて、「埋め込みオブジェクト」を小さく小さくして、「可視しない」状態にしておくといいでしょう。
別に見えてなくてもいいんですよ。このフォーム上のどこかに存在していれば・・・。
「値段」というフィールドの位置づけが変わったことで、「売上入力フォーム」も、若干の手直しが必要ですね。
「商品名」のコンボボックス（商品番号コンボボックス）の表示列から、「値段」を削除しましょう。
え？やり方がわからない？？？うーん・・・細かいところまで覚える必要はないですが、なんとなく「どういうことをやればいいのか」、くらいは、頭に浮かぶようにしといてくださいよ。
大まかに段取りをお話します。
コンボボックスの「値集合ソース」プロパティで↓ビルドボタンをクリックして
下記の3列だけの状態にします。
赤いとこ（フィールドのセレクタ）をクリックして↑黒く反転させてからDeleteキーを押せば消えますからね。
クエリのデザインビューと操作方法は同じです。
で、このクエリビルダのウィンドウを閉じ、「変更しますか？」のメッセージに「はい」ボタンで答えて完了。
さらに、「商品番号コンボボックス」の「更新後処理」イベントで、単価をコンボボックスの2列目から拾うのではなく、DlookupやDmax関数を駆使した方法に書き換えます↓。
Private Sub 商品名_AfterUpdate()
Me!テキスト18 = DLookup("値段", "値段変動テーブル", "商品番号='" & Me!商品番号 & "' and 変更日 = #" & DMax("変更日", "値段変動テーブル", "商品番号='" & Me!商品番号 & "'") & "#")
DoCmd.GoToControl "数量"
End Sub
うひー・・・。いろいろやることがあって、大変です。
でもこれで一応、新しく登録した商品も、今までの商品も、「一番最近変更された値段」が画面に表示され、その値段での「お会計」が計算されて出てくると思います。↓
さあああ・・・・。
このあと、過去の売上金額が変わってしまわないように、「そのときの単価で売上金額を計算させる」ような工夫が必要なわけです。
そう、お察しのとおりで、またこれめちゃくちゃめんどくさいわけで。
たとえば、こんな感じのクエリを作ります↓
「変更日」のところの抽出条件がミソです。
DMax関数でもできると思いますが、サブクエリというやつを使ってみました。
こういう「画面の図」では、抽出条件が長いと全貌をお見せしづらいんですけどもね。。。
そういう場合は、メニューバーの［表示］→［SQLビュー］を選んびます。
なんか、妙な表示になりましたですか？
これがいわゆるSQL文ってやつなんですけどね。
このウィンドウ内に、これを↓ぺたっとコピー＆ペーストしてしまえば、SQL文からクエリをしっかり組み立ててくれるんです。
SELECT 売上テーブル.売上日, 売上テーブル.商品番号, 売上テーブル.数量, 値段変動テーブル.値段, 値段変動テーブル.変更日
FROM 売上テーブル LEFT JOIN 値段変動テーブル ON 売上テーブル.商品番号 = 値段変動テーブル.商品番号
WHERE (((値段変動テーブル.変更日)=(select max(変更日) from 値段変動テーブル where 商品番号=[売上テーブル].[商品番号]
and 変更日<=売上テーブル.売上日)))
ORDER BY 売上テーブル.売上日, 売上テーブル.商品番号;
意味がわからない？？？
んもーーーじゃあ、SQL文を貼り付けるところまでの手順をざっと書きますよ。
クエリを新規に作成して、最初にテーブルを選択する状態になりますでしょう。「テーブルの表示」っていうウィンドウ。
そこで、何もテーブルを選ばずに「テーブルの表示」ウィンドウを閉じて、まっさらのデザインビュー状態にします。
で、メニューバーの［表示］→［SQLビュー］を選ぶと、
こういう状態になると思います↑。
SELECT;とだけ書かれてますけどこれはいらないので消してしまうか上書きするかの要領で、先ほどの長いSQL文をぺたっと貼り付けて、
再びメニューバーの［表示］→［デザインビュー］に戻すと、
テーブル名やフィールド名がまったく同じならば、こうしてデザインが出来上がるはずです。
ご参考までに・・・「変更日」の抽出条件欄には、
(select max(変更日) from 値段変動テーブル where 商品番号=[売上テーブル].[商品番号] and 変更日<=売上テーブル.売上日)
こう入力しています↑
いわゆる、サブクエリってやつですね。
データシートビューに切り替えてみると、変更をしたところで値段が変わってると思います。
これで、
売上の金額も計算ができるようになります↓。
このクエリはこのクエリで、名前をつけて保存しておくといたしましょう。Q_売上一覧 とでもつけようかな。
このクエリを基にして、新しいクエリを別に作りましょう。
で、月ごとの集計表を作ってみると・・・。
2月はまだ値上げをしていなかったので、以前の（値上げ前の）商品単価で売上金額が集計されますが、3月の途中から値上げをしたので、3月の売上はさっきとちょっと変わってると思います。
3月22日分まで売上データを入れた状態で、3月12日に値上げをしたことにして実験しちゃったのでなんだかピンと来ないかもしれませんが・・・。
と、こんな具合に、そこここで結構苦労しないとならないことはならないんですが、テーブルの構造としては非常に合理的なわけです。
SQL得意な人や、データベースの設計に慣れている人は、こういうテーブルの設計の仕方をするケースが多いと思います。要するに、「計算して出せる値は、極力テーブルにフィールドを設けない」ということです。
Q_売上一覧というクエリを基にすれば、さまざまな売上集計や分析をすることができますよね。
ただ、このQ_売上一覧を通じては、追加入力・更新ができないので、売上入力をするときとかはやっぱり苦労して「現時点での値段」をがんばって出さないとならないんですよね。プログラミングの技術のある人ならどうってことないんでしょうが、マイケルにはちょっと辛いかな・・・。
このほかにも、グラフとか、商品の一覧を表示するためのフォームとかも作ってるので、「値段」の出し方を変えたことで、そういうところでも影響が出てしまってないかちゃんと見ておかないとならないですし・・・
マイケル、もうヘトヘトです。
ここまでの状態を、zip形式に圧縮してご用意しました。
mcl_donguri3.zip
なんだかわけわかんない。という場合は、多少役に立つかもしれません。
それでは・・・もうひとつのパターンのほうをご覧に入れましょうか。
 |
|
 |