Inventory onHand method 1

[cimoli]

I am looking at the TradeWinds MS inventory sample. I find in that database, I cannot see the form design mode to see how the query was built.
Is this free file protected or am I doing something wrong?
I want to learn how to show the present inventory Qty, given all the receipts and sales invoice quantities.

Is there some other free sample to study?

 

[cimoli]

I may have found an answer to my question from a Access Book book in my personal library written 2005 by Paul McFedries.
He shows on page 102, a query to calc the onhand inventory quantity. I will experiment with changing his specified criteria date
with a field somewhere to refer to the last inventory taking date. He show what I wondered about. Which is,
how to use a range of receipt Qty and invoiced Qty AFTER the OnHand taking date. Thereby, ignoring past periods receipts and sold Qty.
Nice idea by Paul. Yell if you know of more inventory OnHand calc samples.

 

[SkipVought]

Hi,

On hand can mean different thing depending on how it is calculated. So my definition may not necessarily be in accordance with yours.

Nonetheless it is a cumulative calculation from the time an inventory audit was taken, with a current (at that moment) bin balance or the like, to the present time. CALCULATED!

For instances some inventory systems can allocate (soft or hard allocations) for some order. Do you couunt what's in the bin, or do you subtract what' been allocated? Do you account for soft & hard allocations differently in calculating On Hand? Mad it goes on and on from there.

So the real question is, apart from any automated system, computers, adding machines, how do YOU, or you company, calculate On Hand???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[cimoli]

Hi Skip. I will use the book by Paul McFedries page 102 as my inspiration. I will add 2 fields into my tblItem. OpeningItemsQty and OpeningItemsDate.
So, if i physically count 100 pieces for 1 Item code, I will enter 100 to my OpeningItemsQty and wipe out whatever was in that field before.
Then I would post the corresponding date in OpeningItemsDate.
EG if i counted the 100 pieces for this Itemcode on 4/1/2013, then I will use 4/1/2013 in the OpeningItemsDate.
These 2 fields would be in the query.

I have an InvoicedQty field in my tblInvoicedDetail.
I have a simple need. I realize that i could invoice the item but never really ship it for some reason.
I will think this over later. I want to keep my other Access forms etc in this same database. So it is okay for me to be simple. I am NOT trying to resell my inventory calcs.

I will make a new field in tblItem called OtherQty. As a catch-all field. EG maybe someone stole 15 pieces. I would enter minus 15 in that field.
This would also be in the query.

Finally, the Qry would have what you said too. A calculated "LeftInStockQty" field or some such name.
The query would have totals, as shown by McFedries.

The OpeningItemQty would have a Group By on the totals line.

The InvoicedQty field would have a Sum. The OtherQty would also have a Sum.

The LeftInStockQty calculated field would be OpeningItemQty minus InvoicedQty plus OtherQty.

McFedries uses my InvoicedDate with a Where on the totals line and in the criteria line, he has hardcoded a date
for illustration purposes. I am thinking, I could use my OpeningItemDate instead for the qry to select only
invoicedQty greater or equal to 4/1/2013 for example.

I have to test if i can have a second date criteria for the OtherQty field.
If not, i can work around it somehow.

So now i need to test my theory. thanks to paul mcfederies idea on page 102. Hope it works for me.