running sum query 5

[Pampers]

Hi everyone,
Still working on my little inventory database. Remou showed my how to calculate number of days untill a next stock mutation. Now I'm looking for a way to have a running sum per product, so you can track the inventory level over time. I looked at different solutions, but it is complicated. Here is what I'm looking for in a datasheet view.

Initial stock level (01-01-06): 10 products.

MutDate  Arti  Sold Recei  Stock   
-------  ----  ---- -----  -----
10jan06  1000   5            5
12jan06  1000         8      13 
15jan06  1000   1            12
.....

How could you calculate such a column?
(Grouped By articleCode)


Pampers
Keeping it simple can be complicated

 

[LonnieJohnson]

Put your results in an access report. Then add an extra text box in the detail and play with the running sum property.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[Pampers]

Thanx LonnieJohnson,
but I want the running sum be calculated in the query itself....


Pampers
Keeping it simple can be complicated

 

[Lilliabeth]

http://support.microsoft.com/kb/290136

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[Pampers]

Tnx for the reply,
I tried that approach, but couldn't get it working (dsum). I'll try again...


Pampers
Keeping it simple can be complicated

 

[PaulBricker]

Try this SQL. Substitute your table name for tblRunBalStock.

SELECT tblRunBalStock.MutDate, tblRunBalStock.Arti, tblRunBalStock.Sold, tblRunBalStock.Recei, (SELECT Sum([Recei]) - Sum([Sold]) FROM tblRunBalStock as A WHERE A.[Arti] = tblRunBalStock.Arti and A.[MutDate]<=tblRunBalStock.[MutDate]) AS Balance
FROM tblRunBalStock;


Paul

 

[Pampers]

Tnx Paul,
I will give it a go. Keep you posted.


Pampers
Keeping it simple can be complicated

 

[Remou]

Can I mention that your example seems to have a typo?

[tt]MutDate Arti Sold Recei Stock
------- ---- ---- ----- -----
10jan06 1000 5 -5 'No stock received
12jan06 1000 8 3
15jan06 1000 1 2[/tt]

 

[Pampers]

Hi Remou,
Not really. The initial stock was se at a level of 10 products

Initial stock level (01-01-06): 10 products.

Pampers
Keeping it simple can be complicated

 

[Remou]

Missed that <sheepish grin>.

 

[Remou]

In which case, how about:

[tt]SELECT a.MutDate, a.Sold, a.Recei,
(SELECT [Enter Initial Stock: ]+Nz(Sum([recei]),0)-Nz(Sum([sold]),0)
FROM table1 c
WHERE c.mutdate<=a.mutdate AND c.arti=a.arti) AS Stock
FROM Table1 AS a;[/tt]

 

[Pampers]

Thanx Remou,
I will give that a try too. I can leave out the [Enter Initial Stock: ]-paramater, because I updated the table with the initial stock levels as Received on 01-01-06.

I will be back...

Pampers
Keeping it simple can be complicated

 

[RoyVidar]

Here's another attempt

[tt]SELECT a.MutDate, a.Arti, a.Sold, a.Recei,
NZ(Sum(b.Recei),0)-NZ(Sum(b.Sold),0) as Stock
FROM YourTable a
INNER JOIN YourTable b ON
a.Arti = b.Arti and
a.MutDate >= b.MutDate
GROUP BY a.MutDate, a.Arti, a.Sold, a.Recei
ORDER BY a.Arti, a.MutDate[/tt]

Keep in mind that if there are more than one occurance of Sold or Recei on the same date for the same Arti, it will look a bit funny (same result for those rows) - you might need another field in the join (a.id = b.id)?

Anyway, calculations like this are very demanding, as it will be performed per each row, so you might want to have another look at LonnieJohnsens suggestion - or at least try/time some of the suggestions along the thread.

I'm also wondering - why two columns? Wouldn't negative values indicate sales and positive values indicate received/returned goods? Then if needed, you could use some status column to keep track of special transactions?

Roy-Vidar

 

[Pampers]

Hi RoyVidar,
Tnx for the reply. Indeed I came accross of more then one then one occurance on one date (and it looks funny indeed). I tackled that (for now) by first creating a new table with a new totals-query.

It is demanding, but I find the speed of the query's uptill now satisfactory/good (I used PaulBrickers code - but still experimenting, so I let you know Paul). I'll try your code too, Roy, see how it goes. I keep you posted.



Pampers
Keeping it simple can be complicated

 

[Pampers]

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):

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.

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).

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.

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;

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]

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:

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
Keeping it simple can be complicated