ACCESS 商品毎の最新の単価を設定するには？

質問

ACCESS 商品毎の最新の単価を設定するには？

既に運用しているデータベースで不具合があり、以下のようなクエリを作りたいと考えていますが、データベースを作成してからかなり時間も経ってしまっており、自力で形にすることができません。
お知恵をお借りしたいと思いますので、よろしくお願い致します。

1.テーブル構成
[T_商品マスタ]--商品IDでリレーション設定--[T_単価マスタ]

T_商品マスタのフィールド
商品ID(キー/数値型)
商品名1(テキスト型)
商品名2(テキスト型)
商品分類ID(数値型)
定価(通貨型)
取扱中止(Yes/No型)
販売単位(テキスト型)
備考(メモ型)

T_単価マスタのフィールド
価格履歴ID(キー/オートナンバー)
商品ID(数値型)
仕入単価(通貨型)
仕入先ID(数値型)
販売単価(通貨型)
変更日(日付/時刻型)…Now()にて変更を入力した日付及び時刻を記録しています。

[Q_商品最終単価]…現在運用していますが、仕入単価及び販売価格が[T_単価マスタ]の最新のものにならない不具合が生じています(クエリ内の集計で"最後"を使用していますが最後=最新ではないとか、、、)
クエリ内で設定している項目
商品ID
商品名1(単価を登録する度に変更することは禁止)
商品名2(単価を登録する度に変更することは禁止)
仕入単価(変更の度に[T_単価マスタ]の仕入単価欄に単価を入力して更新)
販売価格(仕入価格を入力するとフォーム上で自動計算)
変更日(仕入価格を入力した日時)

クエリ時のイメージ
商品ID　　商品名1　　商品名2　　仕入単価　　販売単価　　変更日
0001　　　うどん　　 稲庭　　　 100　　　　 200　　　　 2010/01/01 10:10:16
0002　　　うどん　　 讃岐　　　 150　　　　 300　　　　 2010/01/25 11:12:50
0003　　　そば　　　 十割　　　 200　　　　 400　　　　 2009/12/15 15:30:10
.
.
.

伝票入力時にこのクエリを用いて商品IDを入力することで、最新の単価を呼び出すようにしていたつもりでしたが、集計クエリの"最後"に信頼性が無いことがあるそうで、必ずしも最新の単価になっていないと発覚し冷や汗をかいております。

Q_商品最終単価の項目はそのままで、商品ID毎に最新の変更日に基づく最新の単価をクエリできる方法をご教授願います。

回答No.5

piroin654 · ベストアンサー

説明しておきますと、T単価マスタに登録されているものの
すべてについて商品を取得し、それぞれの商品で更新日が
もっとも最近のものを表示する、ということです。
商品名はT_商品マスタから取り込んでいます。
T1というのはT_単価マスタの別名でこれは同じT_単価マスタ
にすることはできません。したがって、WHERE句のところ
だけを変更するということでなく、このSQL文全体で
最新のデータを取得する、ということです。

お礼

とてもご丁寧に有難うございます。

コードを貼付したところ、クエリ上"仕入価格"では無く"定価"が表示されましたので、デザインビューで入れ替えしました。
結果必要なクエリ結果を得ることができました。

この構成を噛み砕いて今後活用させて頂きたいと思います。

ご対応に感動いたしました。
心から感謝いたします。

回答No.4

piroin654

失礼しました。コードの張り間違いです。

SELECT T1.商品ID, T_商品マスタ.商品名1, T_商品マスタ.商品名2, T1.販売単価, T_商品マスタ.定価, T1.変更日
FROM T_単価マスタ AS T1 INNER JOIN T_商品マスタ ON T1.商品ID = T_商品マスタ.商品ID
WHERE (((T1.変更日)=(SELECT Max([T_単価マスタ].[変更日]) AS 変更日の最大 FROM T_単価マスタ GROUP BY T_単価

マスタ.商品ID HAVING T_単価マスタ.商品ID=T1.商品ID)))
ORDER BY T1.商品ID;

これで大丈夫と思います。
何かあれば書き込んでください。

回答No.3

piroin654

追加説明です。

>仕入単価及び販売価格が[T_単価マスタ]の最新のもの

ということですので最新ということは確かに最後に
記録したデータということですが、最新は一番
新しい日付ということでその場合はSQL文にも
書いているように、

Max(変更日)

で最新の日付をとりだします。
なお、掲載したSQL文はQ_商品最終単価の形に
なっています。
提示されたデータをもとに作成したもので、多分
イメージされているものと合致していると思いますが。

お礼

補足欄を無駄に使用してしまったみたいで、"お礼"欄で失礼します。

まず、SQL文をクエリに貼り付けてみたところ、特定の1アイテムの最新の単価が表示されたのみで、全て(商品ID一覧)表示されませんでした。

イメージとしては
[Q_商品最終単価]
0002　　　うどん　　 讃岐　　　 150　　　　 300　　　　 2010/01/25 11:12:50

このように1行だけ表示された状況です。

甘えてコピペそっくりしており、デザインビューでの"変更日"項目の抽出条件には
In (SELECT Max(変更日) FROM T_単価マスタ INNER JOIN T_商品マスタ ON T_単価マスタ.商品ID=T_商品マスタ.商品ID)
という式となりました。

テーブルのリレーションは T_商品マスタ：T_単価マスタ＝1：多 となっています。

T_商品マスタの持つ全てのレコードに対してT_単価マスタの最新の単価が拾えるように教えていただけると幸いです。

補足

ご回答有難うございます。
反応が遅くて申し訳ありません。

現在は作業にかかれる時間を調整しております。

ご回答の内容で検証後、結果を報告いたしますので、遅レスをお許し願います。

回答No.2

bonaron

"最後" ではなく "最大" を使用してください。

補足

ご回答有難うございます。

以前検証したことはありますが、変動あるこの値は常に最新=最大ではないので目的の値は取得できませんでした。

回答No.1

piroin654

このようなものでどうでしょうか。

SELECT T_単価マスタ.商品ID, T_商品マスタ.商品名1, T_商品マスタ.商品名2, T_単価マスタ.仕入単価, T_単価マスタ

.販売単価, T_単価マスタ.変更日
FROM T_商品マスタ INNER JOIN T_単価マスタ ON T_商品マスタ.商品ID = T_単価マスタ.商品ID
WHERE (((T_単価マスタ.変更日) In (SELECT Max(変更日) FROM T_単価マスタ INNER JOIN T_商品マスタ ON T_単価マ

スタ.商品ID=T_商品マスタ.商品ID)));

貼り付けがずれているかもしれませんが。
修正してください。
回答がずれていたらスルーしてください。