MS Access Customer Order Pickers Picking List Auto Choose Locations
Posted on 2016-10-16
This might be a VERY broad question but will aid me in the right direction. After a customer orders there will be a picking list generated for the picker to collect the items on the list.
Our products could be in multiple locations with multiple quantities. (Generally no but could). I have a table called ProdLocations. It has fields:
ProductId(found in the Products Table)
LocID (found in the Locations Table)
I would like for Access to evaluate all the Locations and Quantity in those locations for that product on the order.
Then decide on a location (LocID) that best fits and place it on the pickers list.
Customer orders 5 of productID 32
ProdLocID / ProductID / QtyLoc / LocID
543. 32. 6. 78
777. 32. 2. 99
198. 32. 4 12
Access would say in this scenario ProductID 32 /Qty 5 / LocID 78
Order was for 8 of ProductID then access would suggest LocID 78 and 99 =8
I am trying to understand and figure out the best way to automatically remove product quantities from exact location when there could be multiple locations for a single product.
When a automatic quantity deduction is made we need to know where so we have accurate records.
Advice, Suggestions, etc......
Thank you all for the help!