HI,

I have been a warehouse engineer for several years and found that the
classical approach still works best:
having the tables:

tblProducts
tblPackings (usually 3 levels are enough: pallets (several types)/large
packing like large boxes, bags, drums,.../small packing like small boxes
placed in the large ones, sacs)
tblProductsPackings : which product can come in which bags
tblPurchaseOrders (movements in)
tblPurchaseOrderLines
tblDeliveryOrders (movements out)
tblDeliveryOrderLines
tblLocations
tblStock: what is stored where, this is a table that's filled automatically
with each movement. You need this to now what is stored where and to avoid
tedious calculations using in-out movements.
tblStockMovements (in: when the received goods are physically given a place,
out: when order picking, internal movements like repackings, damaged goods, ..
.)
tblStockMovementLines

Might have forgotten some tables, but this will set you on your way.

greetings
NG



SD707 wrote:
Hi,
I am a new user of Access 2007. I am creating a database to track inventory
quantities by locations. I have a Products table and a Locations table, and I
am wondering what is the best way to set up a Transactions table. We have
three types of transactions:
1. Move products from one location to another.
2. Introduce new products to a location.
3. Deplete products from locations.
We would like to keep a record of transactions, by transaction ID and date.
But the more important function will be to report on current Product
quantities by Location.

What fields should I include in the Transactions table, and with what
relationship types to the other two tables? Do I need more than one
Transaction table? Should I put Add and Remove quantities in separate columns?

Thanks for your help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201005/1