売価変更がある商品の売上金額

質問

access2002

商品マスタ
商品番号,新価格,旧価格,新価格適用日
00000001, 120, 100,2006/10/01
00000002, 200, 180,2006/10/01
00000003, 100, 0,1900/01/01
00000004, 150, 0,1900/01/01
00000005, 220, 200,2006/10/01

売上テーブル
　日　付　,商品番号,売上数
2006/09/30,00000004, 200
2006/09/30,00000005, 20
2006/10/01,00000001, 100
2006/10/01,00000002, 10
2006/10/02,00000001, 80
2006/10/02,00000003, 120
2006/10/03,00000005, 50

上記のテーブルからフォームで日付を入力してクエリを実行し日ごとの売上金額を求めたいと思っています。
フォームの日付 >= 新価格適用日の場合は新価格
フォームの日付 < 新価格適用日の場合は旧価格
ということは分かるのですが、これをどこにどのように組み込んだらいいのかがわかりません。
よろしくお願いします。

回答No.4

noname#60992 · ベストアンサー

No1です。

２回価格が変更になるとどのようなことになるのですか？
商品番号,新価格,旧価格,新価格適用日
00000001, 120, 100,2006/10/01
00000001, 100, 90,2006/11/01
みたいなことになるのかな？　
それとも、２回価格が変更になるということはないのですか？

１回も価格が変更にならないものはどのようなデータなのですか？
商品番号,新価格,旧価格,新価格適用日
00000001, 120,,2006/01/01
こんな感じでしょうか？

このようなものは、どちらかといえば価格変更ログであって、
商品マスタではないと思います。　順当に考えれば商品マスタを
新規に作り直したほうが簡単だとは思います。

このあたりの問題をまったく無視して考えれば、
以下のような[売上金額クエリ]というのを作れば、
動くには動きます。（あまりお勧めしませんが）

SELECT 売上テーブル.日付, 売上テーブル.商品番号, 売上テーブル.売上数, -1*([商品マスタ]![適用日]-[売上テーブル]![日付]>0)*[商品マスタ]![旧価格]-([商品マスタ]![適用日]-[売上テーブル]![日付]<=0)*[商品マスタ]![新価格] AS 販売日価格, [売上テーブル]![売上数]*[販売日価格] AS 売上金額
FROM 売上テーブル LEFT JOIN 商品マスタ ON 売上テーブル.商品番号 = 商品マスタ.商品番号;

２回以上価格変更があるなら動きません。
やはり別途テーブルを作ったほうがよいと思います。

お礼

回答ありがとうございます。
２回価格が変更になると最初の価格はなくなります。常に１世代前の価格しか持てません。
１回も変更されていないデータは以下のようになります。
商品番号,新価格,旧価格,新価格適用日
00000004, 150, 0,1900/01/01

このマスタは、ン千万である会社に作ってもらったシステムの一部です。ですので別途テーブルを作成するにはその会社にお願いしなければなりません。
なぜこんな設計になったのかは分かりません。
そんなわけで取り敢えず既存の形を変えずに考えています。
教えていただいたクエリを試してみます。

回答No.3

CHRONOS_0

>商品マスタは商品の価格が変わるたびにレコードは増えたりしませんが。。。
>どういうことでしょうか？
価格が変わらないのならこの質問自体がないわけですから
価格は変わるわけですよね
で、変わった時、前の価格はどこに書くのですか？
それが旧価格？だとしたらその前の価格はどこに行ってしまうの？

要するにテーブルの設計が悪いですよと指摘しているのですが

回答No.2

CHRONOS_0

いくつか問題点があるようですよ

商品マスタ
マスタというのあまり変動しない半固定的なデータを集めたものにつけます
また商品マスタというからには商品番号が主キーとなるテーブルでなければなりません
アップされたものは商品の価格が変わるたびにレコードが増えていくものですから
マスタではないですね
また、価格の履歴テーブルなら旧価格フィールドは必要ありません
古い日付で旧価格のレコードが残っているはずです
またそういう作りでないと売上日付に対応する価格は引っ張ってこれません

商品番号,価格,価格適用日
00000001, 100, 2005/10/01
00000001, 120, 2006/10/01

このテーブルなら商品番号が同じで売上日付より小さい最大日付のものを取ってくるということになります

もうひとつの方法はNo1さんの案のように
適用開始日だけでなく終了日フィールドも設けたテーブルにするかです
こっちの場合は商品番号で結合し売上日が開始日と終了日の間になる価格を取ってくる
という風に抽出条件が簡単になります

お礼

回答ありがとうございます。
よくわからないのですが、
>アップされたものは商品の価格が変わるたびにレコードが増えていくものですから
>マスタではないですね

これは商品マスタについてのお話でしょうか？
商品マスタは商品の価格が変わるたびにレコードは増えたりしませんが。。。どういうことでしょうか？

回答No.1

noname#60992

このような場合は、商品マスタを
商品番号、価格、開始日、終了日、
０００００００１、１００、２００００１０１、２００６０９３０
０００００００１、１２０、２００６１００１、９９９９９９９９
のように作ったほうが、扱いやすいと思います。

商品番号,新価格,旧価格,新価格適用日
からそのようなテーブルを作ることも簡単にできますし、また再度価格変更があった際もそのまま対応できます。

お礼

回答ありがとうございます。
おっしゃるとおり私もそのようなつくりであれば対応方法も分かるのですが、すでにマスタは作成してあり多量のデータが入力してあるので、できれば現状の形で対応したいと思っています。
この形では無理なのでしょうか。
よろしくお願いします。