|
本帖最后由 LeeTien 于 2010-2-9 16:22 编辑
给你个思路
一般情况下是先入库的
你就先做库存分组合计查询做出入库合计
然后再同样做出库合计
然后再取库存
以下是我做的代码:
SELECT SumGoodsArrivalVouchs.GoodsID, NZ((SumGoodsArrivalVouchs.Quantity),0) AS GoodsArrivalVouchsQuantity, NZ((SELECT SumGoodsAppVouchs.Quantity FROM (SELECT GoodsAppVouchs.GoodsID,Sum(GoodsAppVouchs.Quantity) AS Quantity FROM GoodsAppVouchs GROUP BY GoodsAppVouchs.GoodsID) AS SumGoodsAppVouchs WHERE SumGoodsAppVouchs.GoodsID=SumGoodsArrivalVouchs.GoodsID),0) AS GoodsAppVouchsQuantity, GoodsArrivalVouchsQuantity-GoodsAppVouchsQuantity AS GoodsInventoryQuantity, SumGoodsArrivalVouchs.PurchasePrice/SumGoodsArrivalVouchs.Quantity AS GoodsInventoryPrice
FROM (SELECT GoodsArrivalVouchs.GoodsID, Sum(GoodsArrivalVouchs.Quantity) AS Quantity, Sum(GoodsArrivalVouchs.Quantity*GoodsArrivalVouchs.PurchasePrice) AS PurchasePrice FROM GoodsArrivalVouchs GROUP BY GoodsArrivalVouchs.GoodsID) AS SumGoodsArrivalVouchs;
其中GoodsArrivalVouchs是入库单,GoodsAppVouchs是出库单。
至于你这个的实现方法你可以往里面套 |
|