Removing Products from Locations

[Deonh]

Hi All

I am trying to create a warehousing database ( I know i am probably biting off more than i can chew) First off i am using Access 2013 and i am a entry level user .

I will have 3 tables
tblProducts (all products with there codes go in here) this will be used as a lookup table with fields ProductId; Code ;Description ,Department BarCode

tblLocation with fields LocationID ;Location

third table will be my link table between products and location tables

called tblProductLocationLink

ProductID
LocationID
DateRecieved
QuantityIn
Notes

Up to this point i can get every thing to work.the problem comes in that when i want to pick some of the stock i cant get it to remove the stock from a location and update that location to new status like empty or reduced quantity.

If any one can point me in the right direction i would really appreciate it

 

[plog]

Credits and debits need to be in the same field. The simplest option would be to change tblProductLocationLink:

tblProductLocationLink

ProductID, number, foreign key to Products
LocationID, number, foreign key to Locations
InventoryDate, date, date that inventory was added/taken
InventoryQuantity, number, amount of inventory that was added/taken
Notes

You rename your quantity and date fields so that they refelect the movement of inventory regardless of direction (in/out). And you use negative values for inventory taken from a location.

 

[MarkK]

I don't see how you can do warehouse software without an Order table and a related OrderDetail table. I guess it's possible--though it seems like it would be very rare--that all product movement is only ever a single quantity of a single product. In the real world you mostly buy/sell/transfer a mix of different quantities of different products as a group. Then you can attach the date, origin, destination, customer, supplier--or whatever--to the group of items rather than to each item individually.

 

[jdraw]

I agree with MarkK that your proposed setup may not include all relevant entities.
Here is a link to a data model (doesn't deal with location) Customer, Order, OrderDetails(called OrderProduct), Product, Supplier but hopefully will be helpful to you.

 

[Deonh]

Thanks for all the input working on the base design as soon as have it in place ill respond did not run away yet.