Calculate the selling time of a product 1

[Cabrita]

Hello,

I have this kind of data and my problem is:

-How can I create a query that will return to me the time that each item was in the store before it was sold.

I would be very appreciated if someone could help me. thank you

Code Bar Situation Quantity Date
1223232 Buy 2 01-01-2003
1223232 transfer -2 10-01-2003
1223232 transfer +3 15-01-2003
1223232 Sell 1 20-01-2003
1223232 Sell 1 15-01-2003
4343444 .........................

 

[BNPMike]

You can't. Your data does not allow you to track individual items.

 

[MichaelRed]

BNPMike ,

Not sure that I agree. Of course it is not possible to track individual items, but the general commercial practice is to use (or assume) the oldest item is used first (ye olde FiFo {or Stack} concept).

As long as the &quot;Interval&quot; expected is <= the resoloution of the field, you can calc the difference. Of course if / when the stock Item quantity does not go to zero between positive (buy / Transfer In) and Negative (Sell / Transfer out) transactions.

It is admitedly a less than trivial exercise starting from the structure shown, but certainly not &quot;impossible&quot;.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Cabrita]

Hi MichaelRed,

Thank you very much for your insight, but can you please make a more detailed explanation and how can you make this possible in access? My access knowledge has some limitations.

Thank you very much.

Andre

 

[MichaelRed]

hmmmmmmmmmmmmmmmm ... mmmmmmmmm,

I'm not up for doing hte whole enchallida, but perhaps a SMALL start?

The Table (your woefully small sample)

CodeBar	Trans	Qtty	TransDt
1223232	B	2	1/1/2003
1223232	T	-2	1/10/2003
1223232	T	3	1/15/2003
1223232	S	1	1/20/2003
1223232	S	1	1/15/2003

and, it is missing the woefully incomplete record!

A query Just to make sure the records are in order:

SELECT tblInventory.CodeBar, tblInventory.Trans, tblInventory.Qtty, tblInventory.TransDt
FROM tblInventory
ORDER BY tblInventory.TransDt
WITH OWNERACCESS OPTION;

The Query (sql)
Code]
SELECT TOP 1 qryStockAge.CodeBar, qryStockAge.Trans, qryStockAge.Qtty, qryStockAge.TransDt, qryStockAge_1.TransDt, qryStockAge_1.Qtty, DateDiff(&quot;d&quot;,[qryStockAge].[TransDt],[qryStockAge_1].[TransDt]) AS TmInStock
FROM qryStockAge INNER JOIN qryStockAge AS qryStockAge_1 ON qryStockAge.CodeBar = qryStockAge_1.CodeBar
WHERE (((qryStockAge_1.TransDt)>[qryStockAge].[TransDt]))
WITH OWNERACCESS OPTION;
[/code]


The results

CodeBar	Trans	qryStockAge.Qtty	qryStockAge.TransDt	qryStockAge_1.TransDt	qryStockAge_1.Qtty	TmInStock
1223232	B	2	1/1/2003	1/10/2003	-2	9

(note copy this to NotePad /WordPad and arrange the field names to values. Look for the TmInStock) -- the LAST field. This is the Value for the time in stock for the qtty (9).

Of course it needs a bit of work, but the concept is in place.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Cabrita]

Hi Michael Red,

Thank you very much for your help, I tryed to develop the query that so that it would not calculate the diference between the first two lines, but it would be interative but I cannot do it, any ideas?

Thank you very much

andre