销售成本，库存计算，运行总计 [英] Cost of Sale, Stock calculation, Running Totals
问题描述
我在Access 2010中工作
此查询应该是一个简单的运行求和SQL查询，但我似乎无法弄清楚 - pleeeease帮助，我是在这个问题上度过了一个不眠之夜。我的股票没有平衡，我的销售和购买每天都在继续。
在表格中填写以下字段：
AutoID，DateCreated，BoughtWt ，买了$和SoldCostWt。
ID__Date ______________ BoughtWt_Bought $ _SoldCostWt
1 ___ 2011/04/01 16:16 ___ 5________10000___0
2 ___ 2011/04/02 15:16 ___ 0________0_______1
3 ___ 2011/04/04 14:16 ___ 0________0_______1
4 ___ 2011/04/04 17:03 ___ 5________5000____0
5 ___ 2011/04/04 17： 16___0________0_______2
6 ___ 2011/04/07 20:13 ___ 0________0_______ 3
7 ___ 2011/04/08 19:22 ___ 5________5000____0
计算访问查询中所需的字段为：SoldCost $，StockWt和Stock $。
由于查询结果用于其他查询计算，因此无法在报表中执行此操作。 br />
结果应如下所示：
ID__Date ______________ BoughtWt_Bought $ _ SoldCostWt_已售出成本$ _StockWt_Stock $
1 ___ 2011/04/01 16:16 ___ 5_______10000___0_________0________5______ 10000
2 ___ 2011/04/02 15:16 ___ 0 ____ ________1 __________ 2000_____4______ 8000
3 ___ 2011/04 / 04 14:16 ___ 0_______0_______1 __________ 2000_____3______ 6000
4 ___ 2011/04/04 17:03 ___ 5_______5000____ 0 ________________________________ 11000
5 ___ 2011/04/04 17:16 ___ 04/07 20:13 ___ 0_______0_______3_________4125_____3______ 4125
7 ___ 2011/04/08 19:22 ___ 5_______5000____0_________0________8______ 9125
在一个完美的世界中，以下代数适用于：
出售成本$ =''上一个''股票$ /'''上一个''StockWt *''当前''s'SoldCostWt
StockWt =总和（BoughtWt - SoldCostWt）
股票$ =总和（买入$ -SoldCost $）
I am working in Access 2010
This Query was supposed to be a simple running sum SQL query, but I can’t seem to figure it out – pleeeease help, I am having sleepless nights over this problem. My stocks are not balancing and my sales and purchases are continuing every day.
In the Table the following fields are being populated:
AutoID, DateCreated, BoughtWt, Bought$ and SoldCostWt.
ID__Date______________BoughtWt_Bought$_SoldCostWt
1___2011/04/01 16:16___5________10000___0
2___2011/04/02 15:16___0________0_______1
3___2011/04/04 14:16___0________0_______1
4___2011/04/04 17:03___5________5000____0
5___2011/04/04 17:16___0________0_______2
6___2011/04/07 20:13___0________0_______3
7___2011/04/08 19:22___5________5000____0
The calculated Fields needed in an access query are: SoldCost$, StockWt and Stock$.
This cannot be done in a report as the result of the query is used in other query calculations.
The result should look like the following:
ID__Date______________BoughtWt_Bought$_SoldCostWt_ SoldCost$_StockWt_Stock$
1___2011/04/01 16:16___5_______10000___0_________0________5______ 10000
2___2011/04/02 15:16___0_______0_______1_________2000_____4______ 8000
3___2011/04/04 14:16___0_______0_______1_________2000_____3______ 6000
4___2011/04/04 17:03___5_______5000____0_________0________8______ 11000
5___2011/04/04 17:16___0_______0_______2_________2750_____6______ 8250
6___2011/04/07 20:13___0_______0_______3_________4125_____3______ 4125
7___2011/04/08 19:22___5_______5000____0_________0________8______ 9125
In a perfect world the following algebra such apply:
SoldCost$ = ''Previous''Stock$ / ''Previous''StockWt * ''Current''SoldCostWt
StockWt = sum(BoughtWt - SoldCostWt)
Stock$ = sum(Bought$-SoldCost$)
推荐答案
和SoldCostWt。
ID__Date_____ _________ BoughtWt_Bought
and SoldCostWt.
ID__Date______________BoughtWt_Bought
_SoldCostWt
1 ___ 2011/04/01 16:16 ___ 5________10000___0
2 ___ 2011/04/02 15:16 ___ 0________0_______1
3 ___ 2011/04/04 14:16 ___ 0________0_______1
4 ___ 2011/04/04 17:03 ___ 5________5000____0
5 ___ 2011/04/04 17:16 ___ 0________0_______2
6 ___ 2011/04/07 20:13 ___ 0________0_______ 3
7 ___ 2011/04/08 19:22 ___ 5________5000____0
访问查询中所需的计算字段为：SoldCost
_SoldCostWt
1___2011/04/01 16:16___5________10000___0
2___2011/04/02 15:16___0________0_______1
3___2011/04/04 14:16___0________0_______1
4___2011/04/04 17:03___5________5000____0
5___2011/04/04 17:16___0________0_______2
6___2011/04/07 20:13___0________0_______3
7___2011/04/08 19:22___5________5000____0
The calculated Fields needed in an access query are: SoldCost
，StockWt和股票
, StockWt and Stock
这篇关于销售成本，库存计算，运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
