Inventory-Am I on wrong path?

[Josh]

I'm wondering if I'm setting myself up for problems......am I wrong in my design
of a fairly simple Inventory DB.

This is for receiving only. Some items will be stocked on shelf, but most items
will be delivered directly to various departments.

Most of the samples I see have a "Products" and/or "Inventory" Table, and a
Transactions Table. I only have a Transactions Table. (and P.O. Table).

To get current stock level, I sum the transactions field. (+24 for 24 ItemXYZ
received, -8 for when some was delivered to department).

So, my thinking is why have *any* table that stores an Inventory? But now, as I
said, I'm wondering if I'm missing something obvious....

Also, the P.O. Item(s) when ordered are inserted into the Transactions Table as
a positive, but with Status of "OnOrder". When Recieved, the Status is changed
to "Received". I wonder about the wisdom of doing that, also. Would it be
better to add the P.O. Items at all (to the transactions table) until Received.
If it would be better to do that, then I suppose, to get current stock info
would take sum of Transaction table to get Stock on hand, then add the sum of
P.O. "OnOrder" items (for each item).

Thoughts?
Thanks, Josh

 

[Allen Browne]

Josh, if you can calculate the inventory, there is absolutely no need to
store the current inventory levels. It is far better to take the normalized
approach, and NOT store the dependent value.

I didn't understand the bit about not having a Products table though. If you
store anything other than ItemXYZ, it seems to me that you would need
records for the different products.

You probably do need a way to handle transactions a bit more than you have.
The database must cope with weird things such as:
- You ordered 100, but they actually sent 1000.
- You ordered 500. They sent 100, and backordered 400.
- There were 100 backordered from last time, plus the 200 from a new order,
so they sent 300 at once.
- You ordered 100 for a line that is discontinued. They sent the 24 they
had, but the others will never be sent.

 

[Josh]

It is a rural health center, so we don't "make" or "ship" anything, just
receive. I do have a ItemTable, which list the Items/Parts that we order, which
has PartNumbers, Descriptions, UOM's, prices, Manufactors, VendorID, etc along
with a VendorTable. And, of course, a POTable(purchaseOrders) & POdetails table.

The comment you make reference coping with weird things, is why I thought I
would track that sort of thing in the PurchaseOrderTable. In that table, I have
fields [NumberOrdered], [NumberRecieved], [remarks] which I hope will track that
sort of thing.

Thanks, Josh

 

[mnature]

There is a template available when you are creating a new database on Access,
it is called Inventory Control. Even though it has a column for items sold,
you could simply relabel that as items used. I think it could be useful for
you, because it keeps track of units on hand and units on order. In
addition, you can set a reorder level for any item, and thus have a way of
flagging when you need to order more supplies.

If this could meet your needs most of the way, you can probably get help on
these forums with customizing it.

There is also a template that can be downloaded from the Microsoft site,
which also does Inventory Management. It is slightly different from the
template above, but basically does the same things.

http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT011366681033