アクセス２００３ 各売上日の直近仕入額を知るには？

いつもお世話になっています。

クエリを使って次のようなことがしたいのですが可能でしょうか？

下記のような２つのテーブルがあるとします。

＜仕入記録＞
[仕入ＩＤ]　　[仕入年月日]　　　[商品名]　　　[数量]　　　[単価]
　　１　　　　　2007/04/01　　　　　ＡＡＡ　　　　１　　　　1,000
　　２　　　　　2007/04/03　　　　　ＢＢＢ　　　　１　　　　2,000
　　３　　　　　2007/05/06　　　　　ＡＡＡ　　　　１　　　　1,200
　　４　　　　　2007/05/15　　　　　ＣＣＣ　　　　１　　　　　800
　　５　　　　　2007/06/08　　　　　ＢＢＢ　　　　１　　　　2,200
　　６　　　　　2007/06/20　　　　　ＣＣＣ　　　　１　　　　　900


＜売上記録＞
[売上ＩＤ]　　　[売上年月日]　　　[商品名]　　　[数量]
　　１　　　　　　2007/04/25　　　　ＡＡＡ　　　　　　１
　　２　　　　　　2007/05/08　　　　ＡＡＡ　　　　　　１
　　３　　　　　　2007/05/08　　　　ＢＢＢ　　　　　　１
　　４　　　　　　2007/06/10　　　　ＡＡＡ　　　　　　１
　　５　　　　　　2007/06/10　　　　ＢＢＢ　　　　　　１
　　６　　　　　　2007/06/10　　　　ＣＣＣ　　　　　　１


この時2つのテーブルを使って販売した商品の原価(仕入単価)を求めるクエリを作りたいのですがどのようにしたらよいのでしょうか？
仕入単価は、各商品の売上年月日以前かつ最も売上年月日に近い仕入れ時の単価にしたいと考えています。

上記の場合、希望するクエリの結果は下記のような感じです。

[売上ＩＤ]　　　[売上年月日]　　　[商品名]　　　[仕入単価]
　　１　　　　　　2007/04/25　　　　ＡＡＡ　　　　　　1,000
　　２　　　　　　2007/05/08　　　　ＡＡＡ　　　　　　1,200
　　３　　　　　　2007/05/08　　　　ＢＢＢ　　　　　　2,000
　　４　　　　　　2007/06/10　　　　ＡＡＡ　　　　　　1,200
　　５　　　　　　2007/06/10　　　　ＢＢＢ　　　　　　2,200
　　６　　　　　　2007/06/10　　　　ＣＣＣ　　　　　　　800

ご指導よろしくお願いします。

No.1 ベストアンサー 回答者： noname#140971 回答日時： 2007/05/28 16:22

＜クエリ1＞

ID___売上年月日__商品名__数量__仕入単価
1____2007/04/25__ＡＡＡ_____1_______1000
2____2007/05/08__ＡＡＡ_____1_______1200
3____2007/05/08__ＢＢＢ_____1_______2000
4____2007/06/10__ＡＡＡ_____1_______1200
5____2007/06/10__ＢＢＢ_____1______2200
6____2007/06/10__ＣＣＣ_____1______800

まあ、２つのテーブルと所望のクエリを作成してみました。

SELECT
売上記録.ID,
売上記録.売上年月日,
売上記録.商品名,
売上記録.数量,
DBLookup("SELECT Top 1 単価 FROM 仕入記録 WHERE 仕入記録.商品名='" & [商品名] & "' AND 仕入年月日<=#" & [売上年月日] & "# ORDER BY ID DESC") AS 仕入単価
FROM 売上記録;

と、直近の仕入単価を取得する関数を作成すれば簡単です。
が、これは決して採用してはならないアイデアです。

※テーブル[売上記録]に列[仕入単価]を追加すべし！

理由１、既に、商品名='AAA' の仕入と売上に矛盾を起こしています。
理由２、仕入順に売上するとは限らない。
理由３、質問しなきゃならない判らないややこしいクエリなど利用すべきではない。

と、思います。