DLookup Stock Levels On A Report

[DMT Dave]

Hi Guy's this one should be so simple but finding it difficult, may well be my method



I am trying add a text box on my report in the detail section, there may 5 records on the report

Joe has ordered 5 items from stock, 3 items has got the same product number and 2 are a different product number

I have got a field in the stock called Start Qty, i am trying add a text box to display the current stock level based on Product number, if the first 3 products are Product 452996 and the last 2 products are 451664, what is the formula in the text box to show stock level of that product number ?

Header
Name Product No Warehouse Qty
Detail
Joe 452996 (txtProductNo) Control Source =DLookUp("StartQty","tblStock","ProductNo = txtProductNo")
Joe 452996
Joe 452996
Joe 451664
Joe 451664

Warehouse Qty is not returning anything, have i got this all wrong or the formula wrong ?

Kindest

[CJ_London]

try

=DLookUp("StartQty","tblStock","ProductNo = " & txtProductNo)

Suspect your table design is not correct - you would normally have a table of transactions with a number of transaction types such as opening stock, stock in, stock out, reject, write off, stock adjustment, etc

so you would simply sum the quantity to determine current stock

Otherwise how do you handle it when new stock is delivered/manufactured? or a stock take reveals you have more/less than you think, etc?

[NoellaG]

Hi Ajax,

I worked for several warehouses and all stock software programs have ànd transaction tables ànd a stock table that shows the current stock. You don't want to have to sum a few thousand transactions each time you ask the current stock. So the current stock is updated each time a transaction is confirmed. That's why each year you need to count the stock and make stock corrections where necessary.

[Bob Fitz]

AFAIK conventional wisdom dictates that the current balance of stock should be calculated from a transaction table whenever and wherever the balance is required. The transaction table should be reconciled as and when a physical stock take done. The calculation would only need to be applied to transactions that have taken place since the last stock take.

[NoellaG]

In theory this might be so, but I never actually seen a stock program working this way. If you have several users checking the stock at the same time (both employees on location, and customers via web), calculations won't cut it. For performance reasons we keep the stock up to date with every transaction. Software: PHP for the web app and Access front -end on SQL server for the company software. #Transactions: +/- 500/day. reports via SSRS.

[CJ_London]

It's a matter for debate. I accept that performance is always a consideration but storing calculations of this nature can create other issues. You don't need to necessarily sum thousands of transactions - only those since the last known accurate figure - typically the last stocktake.

The way I handle it for large transaction volumes in the transaction table is to have a transaction quantity field and a stocktake field - so when a stocktake is done, the transaction holds the difference between calculated and actual from stocktake (might be zero) and the stocktake is stored in the stocktake field.

Depends on business practice, staffing levels and typical transaction volume and how stock is organised on the warehouse floor but for large inventories a 'to be stocktaked' report can be generated for those items where the current stock is calculated to be less than say 10 - or each item can have it's own level set (this leads into the Kanban process for reordering). This is a rolling stocktake which auditors will normally accept and means the business does not need to close whilst stock is counted. Usually throw in a few random items as well regardless of stock level as an additional check - idea is to ensure every item is checked at least twice a year.

[NoellaG]

Hi, according to Belgium law we need to close and do an inventory once a year, attended by an official. We also take a stock picture every month and keep this history picture for one year. This helps in case of lawsuits. Storage engineering is a fascinating science, thx for your insights.