How to use Allen Brown inventory functioon

[ezybusy]

Hi,
i am working on an inventory based project where Quantity On Hand is involved, and upon searching the internet, i found Allen Browne's approach to the matter.
However, i have no idea as to how to use it or make it work.
Can something help me, by describing the step-by-step process of using the code to display Quantity On Hand of products ?

Thank you

[pbaldy]

I would think the first question is, do you really need it? I have a couple of fairly large apps where I just use the simpler method of summing the in's and out's for the quantity on hand (with date taken into account for "as of" reports). If you're not going to perform the "stock takes" he describes, the code is probably overkill.

[CJ_London]

the code is documented explaining each step. I use the same principle of using the stocktake date as a starting point for your quantity on hand and summing from there. The only issue is having an accurate date and time since a stocktake taken at the start of the day may vary from one at the end because movements might have occurred during the day.

for example you table might look like

tblItems
itemPK
itemName

tblStockMovement
MovementPK
ItemFK
trantype (e.g. issued, received, stocktake)
trandateT
Qty


simplistically your query might look something like

SELECT tblStockMovement.itemFK, sum(qty) as onHand
from tblStockMovement INNER JOIN (SELECT itemFK, Max(trandateT) AS LastST FROM tblStockMovement WHERE trantype='StockTake' GROUP BY itemFK) AS ST ON tblStock.itemFK=ST.itemFK
WHERE tblStock.trandateT>=ST.LastST
GROUP BY  tblStockMovement.itemFK

this bit is finding the last stocktake

(SELECT itemFK, Max(trandateT) AS LastST FROM tblStockMovement WHERE trantype='StockTake' GROUP BY itemFK)

whilst this bit limits the records to those movements that are on or after the stocktake datetime

WHERE tblStock.trandateT>=ST.LastST

[isladogs]

You could look at the new Northwind 2.0 Developers Edition template which also uses Allen Browne's approach to managing inventory.
This was covered in detail in the Access Europe User Group session on Wednesday and the video will be on YouTube in the next few days.

[ezybusy]

You could look at the new Northwind 2.0 Developers Edition template which also uses Allen Browne's approach to managing inventory.
This was covered in detail in the Access Europe User Group session on Wednesday and the video will be on YouTube in the next few days.

Alright, thank you very much.
I just downloaded the Northwind 2.0 Developers Edition template, which i will look through closely.

[ezybusy]

Thank you very much CJ_London.

[ezybusy]

Well, i guess may not need it not this time. It's a relatively small project, for which i can think of other alternatives.
However, i just want to do a clean work that will not require a lot of tweaking in case the database groes. So i think it's better to go through the hustle to do things right now.