Hi everyone,
Sorry that it took a while to get back. Anyway....
The goal of my queries was to calculate the average stock of a series of products over the year 2006. The database consist of around 250.000 selling and receiving transactions for 4500 products. Once the average stock level was calculated, one can calculate the average turn over speed of every article in stock (TotalSold/AverageStock). Since it is a whole sale company, that figure is important to them.
The layout of the table that holds the selling and receiving transaction was simple (tblStockMutations):
Code:
MutDate Arti Sold Recei
------- ---- ---- -----
10jan06 1000 5
12jan06 1000 8
15jan06 1000 1
15jan06 1000 1
The initial stock levels (01-01-06) where given to me as a txt-file. I updated the tblStockMutations with it.
Code:
MutDate Arti Sold Recei
------- ---- ---- -----
01jan06 1000 10
10jan06 1000 5
12jan06 1000 8
15jan06 1000 1
15jan06 1000 1
Because the table holds more then one entry on a date, I created a new table which is grouped on the MutDate, so only one record exists for one date (for one product).
Code:
MutDate Arti Sold Recei
------- ---- ---- -----
01jan06 1000 10
10jan06 1000 5
12jan06 1000 8
15jan06 1000 2
Then I used Paul Brickers code to create a running balance of the stock (into a new table tblStockMutationsRunSum). Worked like a charm. Indexing this new table gave good speed for the next query.
Code:
SELECT tblStockMutations.MutDate, tblStockMutations.Arti, tblStockMutations.Sold, tblStockMutations.Recei, (SELECT Sum([Recei]) - Sum([Sold]) FROM tblStockMutations as A WHERE A.[Arti] = tblStockMutations.Arti and A.[MutDate]<= tblStockMutations.[MutDate]) AS Balance
FROM tblStockMutations;
Code:
MutDate Arti Sold Recei Bala
------- ---- ---- ----- ----
01jan06 1000 10 10
10jan06 1000 5 5
12jan06 1000 8 13
15jan06 1000 2 11
The last query I used was Remou’s (in a previous post), to calculate the number of days untill next stock mutation, and then inserted the calculated field [Bala*Diff]
Code:
SELECT a.ARTI, a.MUTDATE, a.SOLD, a.RECEI, a.Balance, (Select top 1 b.[mutdate] from tblStockMutationsRunSum b where b.arti=a.arti and b.mutdate>a.mutdate) AS NextDate, [NextDate]-[MutDate] AS Diff, IIf([Balance] Is Not Null,[Diff]*[balance],[recei]*[diff]) AS DaysDiff
FROM tblStockMutationsRunSum AS a
ORDER BY a.ARTI, a.MUTDATE;
This resulted in:
Code:
MutDate Arti Sold Recei Bala Diff Bala*Diff
------- ---- ---- ----- ---- ---- --------
01jan06 1000 10 10 10 100
10jan06 1000 5 5 2 10
12jan06 1000 8 13 3 39
15jan06 1000 2 11 0 0
Finally, I used this last query in a form to calculate the AverageStock (on any given date) and the StockTurnover
AverageStock: Sum(Bala*Diff)/Sum(Diff)
StockTurnOver = Sum(Sold)/AverageStock
Tanx all for the great help and input. And if there are better/faster ways to accomplisch this... let me know.
Pampers
![[afro] [afro] [afro]](/data/assets/smilies/afro.gif)
Keeping it simple can be complicated