Best Practice

[Paul Ilacqua]

I am setting up a system that will receive Component parts in Bins that I
want to track. Each Bin will contain a certain part number. I'm going to
"consume" these parts by building assemblies and blowing out the component
part usage via the Bill of Materials. I'm OK with that part...
I'm undecided on .... how to handle the movement of parts from the bin to
areas in my production floor.
My thought is to set up a receiving table to receive the goods, then
transfer those receipts to a Movement table with a trans code of 0 (Initial
Receipt). All other transactions will be performed within the movement table
with a code 1 transaction being a movement transaction (Does not effect
Total Inventory, just location) and a code 2 transaction (either plus or
minus) for parts removed from bins or moved , other places or scrapped.
I'm worried about a frequent reconciliation with this model to correct my
inventory as there will always be at 1st.

I appreciate any feedback - discussion I could get.

Paul

 

[Golfinray]

I suggest barcodes. Access works great with barcodes, store them all in
tables and query as needed.

 

[Fred]

That's a lot of work, and 2/3 involves operational changes and procedures at
you company rather than database work. Here's an overview that I previously
wrote:

- - -

Most people would define inventory tracking is an information system that
always “knows†the current inventory. Automated inventory systems handle
this by automatically adjusting the inventory numbers for the various
happenings which increase or decrease inventory. For those that don’t
already have this in place, this is a 10 times larger job than they think it
is. So, for many of them, a “less perfect†system that doesn’t fully do
this would be better for them. For example, where you update the inventory
figure by simply entering in then new total count for that item. If not,
read on!

The central “information†system can be paper/card or electronic.
Electronic systems can be of various types such as text documents,
spreadsheets, but most are database based. For this example

Here are the main steps.....always read the later ones before you
start.....where you are headed should always influence what you do.

1. Create unique “names†/ identifiers for all items that you want to
track. The most common example of this is your company’s part number for
the system, following all of the rules for a good part numbering system. An
alternative is somebody else’s part number combined with their name. For
wording in this writing, I will presume you’re using “part number†This
includes defining all units of measure. . E.G. does a part number for rope
mean 1 foot of that rope, one 200’ spool of that rope etc.

2. Define your “sphere†of what will be considered to be “in†your
inventory. Is this your stockroom, your whole building, your whole
company, a single service truck?

3. List all of the current ways in your company that inventory of an item
in your “sphere†could be modified*. The 4 main categories of this are:




Income
Outgo (sales, consumption etc.)
Creation*
Destruction*

* E.G. If, by a manufacturing act, you use part a and part b to build part
c, that act “destroys†A & B and “creates†C

The results must be

4. Set up and implement procedures, rules, practices to make sure that
every instance of every item #3 will get recorded as a transaction for each
part number involved, and that happenings not under #3 are not recorded.*
Usually, this requires defining a mental or physical around your “sphereâ€.
For example, if your “sphere†is (only) your stockroom and your production
area, then your procedures must make sure that moving an item from your
stockroom to your production area is never recorded as a transaction, and
moving an item from either of those two areas to elsewhere is always recorded
as a transaction.

5. Get / Set up a data system which has a current inventory quantity for
each part number, and which supports receiving each recorded transaction and
making the appropriate modifications to inventory quantities for each
recorded transaction.*

6. Make sure each transaction gets processed in the data system.*


Note: A portion of the items under #3 (and processed under #4, #5 & #6)
might be, or are set up as happenings within the same data system.
Examples might be sales, shipments, usage on work orders, instances of
manufacturing, “receiving†etc.. In these cases, decide which of these your
data system can and will interpret and execute as inventory transactions, and
get/make a data system that properly does so. Then, when implementing your
procedures under #4, consider entry/execution of such an action in the data
system to also be recording of the inventory transaction


- - -

This covers tracking inventory quantity. Your "location" discussion addes a
new set of questions and another dimension to this.

Sincerely,

Fred