You'll need to store inventory information separate from the item table, linked by item_id.
Also would be best to have a table for locations.
The inventory table will have at least location_id, item_id, qty and all the extra info about the exact location of the item.
Thanks for responding. I'm kinda new to this so please pardon my slowness.
So I need to create an inventory table with item_id, location_id, qty and anything else. then link this table to the main table by creating a relationship between the two tables using the item_id.?
You mentioned it was best to have a location table. What would I store in that one? and would I like the location_id to that?
Location table is what stores the ID ( primary key serial number ) and the name of the location.
Location is what uniquely identifies the main group where inventory items are. This will be:
1 Donations Center
ID LocationID ItemID ( or Part Number ) Qty Bin
1 1 1 5 The bin in Donations Center
2 2 1 2 The bin in Warehouse
The Bin is kinda optional because maybe you don't bin stuff at the Center but you do in the Warehouse.
Depending on where you use the app you can default your inventory context to a Location by it's ID.
But that's a bit more involved and at the moment you can just concentrate on getting this right.
So when this is set up, you transfer your inventory from location to location by some scripted method and some global fields for selections.
You'll need begin location id ( this you can get from current / selected record in inventory ), end location id and the change qty.
Decrease qty from begin location, increase in end location by the change qty.
I followed your direction and created a separate table for locations and i was able to assign different locations to each of the items. This worked great.
My next question is how do I get the total of each item based on location? I would like to see this on the same layout when I pull up the record for each item. In my head I think the formula should be (Sum (" Units In" where location = Warehouse) I tried many different variations of this with no luck. Any suggestions?