ACCESS 查询物料进出明细时,计算并显示当时库存

效果如下图,[数量]字段上的负数为退货或者出库:

以下是入库单与出库单数据表里的数据:

 这里要用到一个临时查询,我随便取了个 "物料进出查询",词不达意,也懒得改了,大家不要介意

SELECT 入库单.产品ID, 入库单.入库数量 AS 数量,入库日期  as 日期 FROM 入库单
UNION ALL 
SELECT 出库单.产品ID, 出库单.出库数量*-1 AS 数量,出库日期  as 日期 FROM 出库单;

这里要注意,不要自作多情用括号把两个SELECT括起来,不然会报错.原因是ACCESS里面,不能把UNION/UNION ALL前后语句括起来.

数据展示如下

 接下来,我们给这个临时表插入一个[当时库存] 字段

SELECT T1.日期, T1.产品ID, T1.数量, 
     (SELECT SUM(T2.数量)
     FROM 物料进出查询 AS T2
     WHERE T2.产品ID = T1.产品ID
       AND T2.日期 <= T1.日期) AS 当时库存
FROM 物料进出查询 AS T1
ORDER BY 日期;

这里最好按日期排一下序，不然【当时库存】 会让人看起来感觉比较乱

我SQL水平比较菜,在这里陷入了一个误区,老想着用这个表联接它自己,各种INNER JOIN / LEFT JOIN / RIGHT JOIN 操作,结果都带有重复数据.后来想想,我目的只是增加一列 [当时库存],应该用子查询才对啊,这才扳正了方向.

如果要在VBA中调用这个查询并指定一个物料，就这么写：

CurrentDb.Execute " select * from 查询1 Where 产品ID=" & Textbox1.Value

这里顺便说说子查询:

在Access中，子查询是指在一个查询中嵌套了另一个查询。子查询可以用作  条件、 字段 或 来源表.子查询可以放在SELECT、FROM、WHERE或HAVING子句中。以下给出几个例子,方便大家理解

1.作为条件:
SELECT * FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderDate > #2023-01-01#)

    和用在WHERE中差不多,用在HAVING中的情况如下:
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID
HAVING TotalSales > (SELECT AVG(TotalSales) FROM (SELECT ProductID, SUM(SalesAmount) AS TotalSales FROM Sales GROUP BY ProductID) AS SubQuery)

2.作为字段(文章上面的案例就是如此):
SELECT CustomerID, OrderCount = (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = Customers.CustomerID) 
FROM Customers

3.作为来源表:
SELECT * FROM
(SELECT CustomerID, SUM(OrderAmount) AS TotalAmount FROM Orders GROUP BY CustomerID) AS SubQuery

子查询也是可以套娃的,比如这辆"三套车":

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE OrderID IN (
        SELECT OrderID
        FROM OrderDetails
        WHERE ProductID = 123
    )
)