我创建了一个包含 Part In 和 Part Out 的备用清单。如下图所示。
如您所见,有 Part In、Part Out 和 Stock Balance。当我输入 Part In 值=1 时,库存余额值等于 1,当我输入 Part Out 值时,它将扣除库存余额值。问题是如何将第 1 行的 Stock 余额值捕获到第 2 行?输入新值时的想法 Part In :
[Stock Balance row 2 = Stock Balance from row 1 + Part In]
我希望你能理解我的解释并帮助我。谢谢你。
由于评论太长,我将添加此作为答案以充实我的评论
最好在表中包含三个字段 - 交易日期/时间、交易方向(进出)和交易中的零件数量。然后您可以添加所有部分,减去所有部分并在任何时间点获得余额
运行以下 VBA 以创建一个包含三个字段的表并添加一些示例数据:
Sub Test()
With DoCmd
.SetWarnings False
.RunSQL "CREATE TABLE Stock (" & _
"Date_Time DateTime, Direction CHAR, Unit INTEGER)"
.RunSQL "INSERT INTO Stock VALUES(#2017/11/20 07:00:00#, 'In', 100)"
.RunSQL "INSERT INTO Stock VALUES(#2017/11/20 07:00:01#, 'Out', 5)"
.RunSQL "INSERT INTO Stock VALUES( #2017/11/20 07:00:02#, 'In', 3)"
.RunSQL "INSERT INTO Stock VALUES(#2017/11/20 07:00:05#, 'In', 2)"
.RunSQL "INSERT INTO Stock VALUES(#2017/11/20 07:10:00#, 'Out', 15)"
.SetWarnings True
End With
End Sub
此 SQL 将为您提供余额的运行总计:
SELECT T1.Date_Time
, T1.Direction
, T1.Unit
, (
SELECT SUM(Unit)
FROM Stock
WHERE Direction='IN' AND Date_Time<=T1.Date_Time
) -
NZ((
SELECT SUM(Unit)
FROM Stock
WHERE Direction='OUT' AND Date_Time<=T1.Date_Time
),0) AS Balance
FROM Stock T1
此 SQL 将在特定时间点给出余额:
SELECT Date_Time AS TimeFrame
, (
SELECT SUM(Unit) AS Stock_In
FROM Stock
WHERE Direction='In' AND Date_Time<=#2017/11/20 07:00:05#
) -
(
SELECT SUM(Unit) AS Stock_Out
FROM Stock
WHERE Direction='Out' AND Date_Time<=#2017/11/20 07:00:05#
) AS Balance
FROM Stock
WHERE Date_Time=#2017/11/20 07:00:05#
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句