Inventory movement

[vbnetman]

Hello experts,
I am working on an inventory db. I need some general help in determining the
relationship between 2 of the tables; TblParts and TblTransactions. I store
part numbers in the parts table and the transaction table serves as a
historical record of the movement of those parts. Is the movement of a part
considered 1 transaction or can 1 transaction include the movement of 1 or
more parts. Can someone shed some light on the thought process and how this
is normally handled?

Thank you

 

[Lynn Trapp]

It all depends on the level of complexity you need. Inventory management can
range from pretty simple to extremely complex. Is your company a retail
store, a supplier, and/or a manufacturer. In any of those cases, you are
likely to need at least 3 tables -- tblParts, tblInventory, and
tblInventoryTransactions. The table tblInventory would store your current
inventory and the table tblInventoryTransactions would store the multiple
detail of each transaction. If your company is a manufacturer, then it gets
much more complex depending on the kind of manufacturing you do. Discrete
manufacturing is simpler than Process manufacturing. Also, you will need to
account for the possibility of multiple storage locations -- i.e. multiple
warehouses.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[vbnetman]

Hi Lynn,
Thank you for the reply. There are several other tables involved in a
somewhat complex db. From a relational standpoint I need clarity about a
transaction. Seems that a single part can only be moved once in a single
transaction (I would not buy something and return it at the same time).

It all depends on the level of complexity you need. Inventory management can
range from pretty simple to extremely complex. Is your company a retail
store, a supplier, and/or a manufacturer. In any of those cases, you are
likely to need at least 3 tables -- tblParts, tblInventory, and
tblInventoryTransactions. The table tblInventory would store your current
inventory and the table tblInventoryTransactions would store the multiple
detail of each transaction. If your company is a manufacturer, then it gets
much more complex depending on the kind of manufacturing you do. Discrete
manufacturing is simpler than Process manufacturing. Also, you will need to
account for the possibility of multiple storage locations -- i.e. multiple
warehouses.

Hello experts,
I am working on an inventory db. I need some general help in determining

[quoted text clipped - 10 lines]

Thank you

 

[Lynn Trapp]

transaction. Seems that a single part can only be moved once in a single

transaction (I would not buy something and return it at the same time).

This is true, but depending on the nature of your inventory requirements, a
single transaction might actually spawn multiple transactions. For example,
if you need to move a part from warehouse A to warehouse B, then you might
need a move order transaction. Then when the part arrives at warehouse B,
you might need a receiving transaction. At the same time, if you want to
keep track of parts as they move from warehouse to warehouse, then you might
need an intransit transaction for while the part is moving from one location
to the other.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html