クエリで、1つ前の在庫数を表示するには

質問

Accessのバージョン　2002

品目に対してレコードが更新されたら、在庫数の変化量を調べるための「1つ前の在庫数」と言うフィールドを作成してクエリを作成しているところですが、品目がレコード順に並んでいれば、問題ありませんが、当然ながら色んな品目が順不同でデータを入力していきます。

下記の式の場合、途中で別の品目のデータが入っていると、品目をフィルターにかけると、別の品目の在庫数のデータが表示されます。

1つ前の在庫数:DLookup("在庫数","在庫数","ID="&[ID]-1)

ID 　No. 　　品目 　　在庫数 　1つ前の在庫数
1 　1 　　　みかん 　　100
2 　2 　　　みかん 　　98 　　100
3 　3 　　　みかん 　　95 　　　98
4 　4 　　　みかん 　　90 　　　95
5 　5 　　　みかん 　　80 　　　90
6 　6 　　　みかん 　　65 　　　80
7 　7 　　　みかん 　　95 　　　65
8 　8 　　　みかん 　　60 　　　95
9 　9 　　　みかん 　　50 　　　60
11 　11　　　みかん 　　55 　　　150

IDが11の部分の「1つ前の在庫数」が、150となっています。

この150は、別の品目の在庫数になります。

品目をフィルターにかけた時、IDの9の在庫数50が、IDの11の1つ前の在庫数に、IDの9の在庫数50を表示する様にしたいです。

ご教授お願い致します。

回答No.1

noname#231195 · ベストアンサー

ご質問に答えるだけなら、こういう風にすることができます。

1つ前の在庫数: DLookUp("在庫数","在庫数","ID=" & Nz(DMax("ID";"在庫数","ID<" & [ID] & " AND 品目='" & [品目] & "'"),0))

要するに、[ID]-1の代わりに、そのレコードが示す品目のすべてのレコードのうち、IDがそのレコードのIDより小さい最大のIDを

DMax("ID";"在庫数","ID<" & [ID] & " AND 品目='" & [品目] & "'")

で拾ってきます。
ただ、該当するレコードがない場合はNullが返ってきて、DLookUpがエラーを出してきますので、NzでNullを0に変えています。
エラーが表示されてもいいのならNz(... , 0)の部分はいりません。

お礼

Firefoxの設定が変わっていましたので、お礼コメント入力で時間が掛かりましたが、無事解決出来ました。

有難うございました。