【Access】DSUM関数をサブクエリに【SQL

質問

Accessで、DSUM関数を使っているものを、サブクエリに変更したいです。クエリでは、3/15の原料ごと・部署ごとの使用量と、3/15時点での原料ごとの残量を出します。どのようにすればよいでしょうか。よろしくお願いします。

【原料テーブル】
原料ID 使用可能量
1 1000
2 2000

【使用履歴テーブル】
原料ID 使用部署 使用量 使用日
1 1 20 2022/03/02
1 2 10 2022/03/02
1 1 30 2022/03/08
1 1 110 2022/03/15
1 2 120 2022/03/15
2 3 130 2022/03/15

【クエリ】
SELECT 使用履歴.原料ID, 使用履歴.使用部署, 使用履歴.使用量, 使用履歴.使用日, [使用可能量]-Nz(DSum("使用量","使用履歴","原料ID=" & [使用履歴].[原料ID] & "AND 使用日<#2022/3/15#"),0) AS 使用可能残量
FROM 使用履歴 INNER JOIN 原料 ON 使用履歴.原料ID = 原料.原料ID
WHERE (((使用履歴.使用日)=#3/15/2022#))
ORDER BY 使用履歴.原料ID, 使用履歴.使用部署;
【実行結果】
原料ID 使用部署 使用量 使用日 使用可能残量
1 1 110 2022/03/15 940
1 2 120 2022/03/15 940
2 3 130 2022/03/15 2000

回答No.3

dell_OK

結果は違うのですが、3/15を含まない場合はこんな感じになりました。

【クエリ】
SELECT 使用履歴.原料ID, 使用履歴.使用部署, 使用履歴.使用量, 使用履歴.使用日, 原料.使用可能量-使用量 AS 使用可能残量
FROM (SELECT 使用履歴.原料ID, 使用履歴.使用部署, MAX(使用履歴.使用日) AS 使用日, SUM(IIF(使用履歴.使用日 < #2022/3/15#,使用履歴.使用量,0)) AS 使用量 FROM 使用履歴 WHERE 使用履歴.使用日 <= #2022/3/15# GROUP BY 使用履歴.原料ID, 使用履歴.使用部署) AS 使用履歴 INNER JOIN 原料 ON 使用履歴.原料ID = 原料.原料ID
ORDER BY 使用履歴.原料ID, 使用履歴.使用部署;

【実行結果】
原料ID 使用部署 使用量 使用日 使用可能残量
1 1 50 2022/03/15 950
1 2 10 2022/03/15 990
2 3 0 2022/03/15 2000

お礼

ありがとうございました。

回答No.2

dell_OK

使用日の条件が＝になっていました。
3/15を含めて集計する場合は＜＝にしてください。
3/15を含めないで集計する場合はもう少し複雑になりそうです。
ＤＳＵＭと同じ結果にされたいのでしたら、改めて考えさせてください。

お礼

ありがとうございました。

回答No.1

dell_OK

こんな感じでしょうか。

【クエリ】
SELECT 使用履歴.原料ID, 使用履歴.使用部署, 使用履歴.使用量, 使用履歴.使用日, 原料.使用可能量-使用量 AS 使用可能残量
FROM (SELECT 使用履歴.原料ID, 使用履歴.使用部署, MAX(使用履歴.使用日) AS 使用日, SUM(使用履歴.使用量) AS 使用量 FROM 使用履歴 WHERE 使用履歴.使用日 = #2022/3/15# GROUP BY 使用履歴.原料ID, 使用履歴.使用部署) AS 使用履歴 INNER JOIN 原料 ON 使用履歴.原料ID = 原料.原料ID
ORDER BY 使用履歴.原料ID, 使用履歴.使用部署;

【実行結果】
原料ID 使用部署 使用量 使用日 使用可能残量
1 1 110 2022/03/15 890
1 2 120 2022/03/15 880
2 3 130 2022/03/15 1870

お礼

ありがとうございました。