Access/VBA - Intelligent Stock Allocation 1

[overdraft015]

In our warehouse we hold stock in individual storage locations (bins), 1 product could be in more than one bin and each bin could contain multiple products. The qty of any product can vary in any bin.

What i want to do is when an order is "released" to the warehouse, is to create a pick list for the warehouse. The pick list should list all the bins that the user has to visit and tell them the product / qty to pick in that bin.

That seems like it should be relatively simple to accomplish but what i would also like it to do is offer the "quickest" and "tidiest" solution for picking. what i mean by this is say the order needs product A01 x10, product B01 x10, product C01 x10.

Product A01 is in: bin001 (x5) - bin002 (x5) - bin003 (x100)
Product B01 is in: bin002 (x1) - bin003 (x10)
Product C01 is in: bin001 (x10) - bin003 (x10)

Bin001 is a pick priority 1 (highest)
Bin002 is a pick priority 1 (highest)
Bin003 is a pick priority 2 (lowest)

so in my mind the quickest and tidiest would be to pick:

product A01 from bins bin001 x5 and bin002 x5 (i know this is not the quickest because we could pick the whole lot from bin003 but this would create an addition part pallet so i would only recommend picking from a lower priority bin when you can pick the whole of that qty from that location and leave no remainder)

product B01 from bin003 (x10) (lowest priority but this leaves none of this product in that bin)

Product C01 from bin001 (x10) (because this is the highest priority pick bin)

Hope this makes sense and someone can help me

Thank you,

Michael

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

Not sure of your objective function.

I interpret this to say that you always want to draw from a bin first if you can copletely empty a bin, followed by drawing from the highest priortity bin. You want to maximize the most emptied bins. To verify, how do you handle

bin 1: 5 items pri 2
bin 2: 5 items pri 2
bin 3: 10 items pri 1
bin 4: 20 items pri 1

if I need 11 items I could draw all of bin 1 and bin 2 and then from bin 3. This emptys 2 pri 2 bins then draws from a pri 1 bin. Or I can draw from bin 3 and then from bin 4. This emptys 1 pri 1 bin then draws from a pri one bin.

My assumption was that the most amount of empty bins is the objective.

It seems relatively easy, to make a heuristic solution, but would not know where to start to provide you something. Do you have actual fields? Do you have an desired output concept? What is the interface and output.

My guess is you probably have fields like

binID
productID
productBinqty (the quantity of the product in a bin)
binPriority

So here is some pseudo code.

public sub getRecommendeddraw(product, qty)
(pick the bins that you can empty)
do until no returned records or qty = 0

select bin where product = productID and productBinqty < order by binPriorty

choose the bin and amount selected = productBinqty
qty = qty - product bin qty
loop

If you still have qty then pick from the highest bin.
select bin where product = productID order by bin priority
basically do the same loop.

end sub

I could probably write a quick procedure to do this with some more information.

 

[overdraft015]

Thank you MajP for your time. Yes the idea is to pick a bin if i can empty it of that product (not every product in that bin unless the rest of those products are on that order) and if i cant then pick in priority then name order.

The goal is not neccesarily to maximize the empty bins but is to visit as few bins as possible (without creating more part pallets) - hope that bit makes sense.

Here is my test data in the test app im working with.

My Orders Table
----------------
Order Product Qty
ORD01 PROD01 10
ORD01 PROD02 10
ORD01 PROD03 10
ORD02 PROD01 10
ORD02 PROD02 10
ORD02 PROD03 10

Stock Locations
---------------
Product Bin Qty Priority
PROD01 BIN01 4 1
PROD01 BIN02 7 2
PROD01 BIN03 10 2
PROD02 BIN01 18 1
PROD02 BIN02 3 2
PROD02 BIN03 100 2
PROD03 BIN01 1 1
PROD03 BIN02 20 2
PROD03 BIN03 42 2


So i process the first order and this is the pick i think should be generated

Order Product Bin Qty
ORD01 PROD01 BIN03 10
ORD01 PROD02 BIN01 10
ORD01 PROD03 BIN01 1
ORD01 PROD03 BIN02 9


So this leaves me with this stock

Product Bin Qty Priority
PROD01 BIN01 4 1
PROD01 BIN02 7 2
PROD02 BIN01 8 1
PROD02 BIN02 3 2
PROD02 BIN03 100 2
PROD03 BIN02 11 2
PROD03 BIN03 42 2


so i now process the second order which i work out the pick should be

Order Product Bin Qty
ORD02 PROD01 BIN01 4
ORD02 PROD01 BIN02 6
ORD02 PROD02 BIN01 8
ORD02 PROD02 BIN02 2
ORD02 PROD03 BIN02 10

Hope this helps. If you need more details then im sure i can explain further.

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

Please find my test data attached. No attemps of any coding in this.

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

I think this is close, my algorithms get different results. I need to understand you optimization. What do you mean by partial pallet? Lets say your data was a little different

Here is the stock for Prod2
PROD02 BIN01 18 1
PROD02 BIN02 3 2
PROD02 BIN03 6 2

and you are drawing 10.

You currently pull everything from Bin 01 which is one draw and one partial pallet (I think).

I could also draw all of bin02 and bin03 and then 1 from bin 01. This means I get to empty two bins, but it requires 3 draws.

Now how about needing nine. I could draw all form Bin01, or all from Bin02 and Bin03. I still am not certain what is preferred.

This makes the draws, updates Stock, and updates Pick.

Public glblNeededQuantity As Integer
Public glblProductID
Public Function getProductID() As String
  getProductID = glblProductID
End Function

Public Function getNeededQuantity()
  getNeededQuantity = glblNeededQuantity
End Function


Public Function getWholeBinID() As String
  'The amount needed is more than what is in a bin
  getWholeBinID = Nz(DLookup("Bin", "qryLessThanRequired"), "None")
End Function
Public Function getWholeBinQty() As Integer
  'The amount needed is more than what is in a bin
  getWholeBinQty = Nz(DLookup("Qty", "qryLessThanRequired"), 0)
End Function
Public Function getPartialBinID() As String
  'The amount needed is less than what is in a bin
  getPartialBinID = Nz(DLookup("Bin", "qryMoreThanRequired"), "None")
End Function
Public Function getPartialBinQty() As Integer
  'The amount needed is less than what is in a bin
  getPartialBinQty = Nz(DLookup("Qty", "qryMoreThanRequired"), 0)
End Function

Public Sub processOrderItem(productID As String, orderID As String, qty As Integer)
  Dim binID As String
  Dim binQty As Integer
  Dim strSql As String
  glblNeededQuantity = qty
  glblProductID = productID
  binID = getWholeBinID
  binQty = getWholeBinQty
  Do Until binID = "None" Or glblNeededQuantity = 0
    'update Pick
    strSql = "INSERT INTO Pick ( Product, Bin, Qty, [Order] ) "
    strSql = strSql & "SELECT Product, Bin, Qty, '" & orderID & "' FROM qryLessThanRequired"
    'Debug.Print strSql
    CurrentDb.Execute strSql
    'Update the Stock
    strSql = "UPDATE Stock SET Stock.Qty = 0 WHERE Bin = '" & binID & "' AND Product = '" & glblProductID & "'"
    'set the required amount
    glblNeededQuantity = glblNeededQuantity - binQty
    'Debug.Print strSql
    CurrentDb.Execute strSql
    binID = getWholeBinID
    binQty = getWholeBinQty
  Loop
  'All whole bins used up now grab a partial amount from a larger bin
  binID = getPartialBinID
  binQty = getPartialBinQty
  'Debug.Print binID & " " & binQty
  If glblNeededQuantity = 0 Then Exit Sub
  If binID = "None" Then
    MsgBox "Can not fill order"
    'add code here to handle this case
  Else
    'update Pick
    strSql = "INSERT INTO Pick ( Product, Bin, Qty, [Order] ) "
    strSql = strSql & "SELECT Product, Bin, " & glblNeededQuantity & ", '" & orderID & "' FROM qryMoreThanRequired"
    'Debug.Print strSql
    CurrentDb.Execute strSql
    'Update stock
    strSql = "UPDATE Stock SET Stock.Qty = " & binQty - glblNeededQuantity & " WHERE Bin = '" & binID & "' AND Product = '" & glblProductID & "'"
    CurrentDb.Execute strSql
  End If
End Sub

Public Sub processOrders()
  Dim rs As DAO.Recordset
  Dim productID As String
  Dim orderID As String
  Dim qty As Integer
  
  Set rs = CurrentDb.OpenRecordset("Orders")
  Do While Not rs.EOF
    productID = rs!product
    orderID = rs!Order
    qty = rs!qty
    processOrderItem productID, orderID, qty
    rs.MoveNext
  Loop
  
End Sub

then you need these two queries

qryLessThanRequired

SELECT TOP 1 Stock.Product, Stock.Bin, Stock.Qty, Stock.Priority
FROM Stock
WHERE (((Stock.Product)=getProductID()) AND ((Stock.Qty)<=getNeededQuantity() And (Stock.Qty)>0))
ORDER BY Stock.Qty DESC , Stock.Priority;


qryMoreThanRequired

SELECT TOP 1 Stock.Product, Stock.Bin, Stock.Priority, Stock.Qty
FROM Stock
WHERE (((Stock.Product)=getProductID()) AND ((Stock.Qty)>getNeededQuantity()))
ORDER BY Stock.Priority, Stock.Qty;

This is pretty close but I always try to empty as many bins first before I pull from a bin that has more than I need.

 

[overdraft015]

Wow MajP you was up late, i hope it wasnt just for this. but it is very much appreciated.

I think there is a difference between the way you have done it and the way the warehouse manager thinks it should work. His ultimate goal is to try visit as few bins in one pick without creating the part pallet.

I shall explain what i mean by part pallet which is where i think i havent been very accurate in helping you help me.

Part pallet. in our warehouse we have what is classed as an upper bin (priority 2) and a lower bin (priority 1). The upper bins need to be accessed using a fork lift truck and are always (nearly always) full pallets of the product and the lower bins can be accessed by just walking around the warehouse and can just be 1 or 2 cartons of the product here and there. So he only wants to visit an upper bin if there is no stock on the lowers or if he will empty the upper bin. This is because he deems that the lower bins get full quicker than the uppers therefore they should be picked first, with the exception of the full pallet pick purely to save time without creating lots of part pallets.

I hope this makes a little bit more sense. what you have done works well for the management of the stock but there is another project underway group most products together (but will still be held in both upper and lower bins because there is not enough fork lift trucks for everyone to access one)

Thank you again for your time and expertise on this.

p.s. we are a large glassware, tabletop manufacturer so if there is any plates, cups, glasses knifes, forks etc.. that you would like i am happy to send you some for all your effort in helping me.

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

Ok I am getting closer in understanding what you are trying to optimize and what are the constraints, but I do not think it is still clear.

I think you want to minimize the amount of bins visited
But there are a few constraints that are not clear.

In the example

you need 10 for Prod03 and the stock is

PROD03 BIN01 1 1
PROD03 BIN02 20 2
PROD03 BIN03 42 2

If I was purely minimizing the visits. I would go to bin 2. This creates 1 visit and I think 1 partial pallet.

I interpret a partial pallet to be a draw from an upper bin where you do not empty an upper bin.

However you draw from BIN01 and the rest from BIN02. This causes 2 Visits and I assume I partial pallet.

So there has to be a constraint that you are not explaining for why I would make more visits while still creating the same amount of partial pallets.

I assume once you draw from an upperbin creating a partial pallet, you move it down and change it to a priority 1 bin? If not, how do I know that bin is now a partial pallet bin?

 

[overdraft015]

you are right in the way you think that yes by my logic you would still be visiting 2 bins and still creating a partial pallet but with that you would never create more than 1 partial pallet in the upper bins.

If we didnt do it this way then say we had 10 full pallets of 20 cartons each in the upper bins and 5 cartons in the lower bins and i did an order for 15 cartons then by using the visit as few bins a possible senario then i would go to the first upper bin and take all 15 in 1 visit. this leaves me with 5 cartons in the lowers, 5 cartons on an upper pallet and 9 full pallets. if i repeat this order i would then visit another full pallet and take from that creating another part pallet. this would continue untill all the full pallets are now part pallets and i am not using my pallet spaces efficiently.

hope that explains the reason. so yes you are right there is an additional constraint when i think about it and that is there should only ever be 1 part pallet in the uppers. this could be set based on a pallet qty field or we can always assume that the first upper bin will be the first pallet to be used and therefore the only part pallet.

Michael.

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

sorry i forgot to answer the last part of your reply.

We would run what we call a replenishment report which tells us that we need to move down some stock for the lower bins. So we only do this if we are runnig out of space in the upper bins or if there is no stock on the lower bins for that product.

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

Sorry but I am getting more confused.
1) From what you are showing what is a partial pallet and how do I know if I created a partial pallet. I think that your example assumes a full pallet has 20 cartons. Is that always the case? If that is not always the case (and I assume it is product specific), you can not do this without a pallet quantity field. You need to explain this better.

I assume you need a product table
tblProduct
productID
productName
unitsPerPallet (number of cartons, cases, sets, etc. per pallet)
other product fields

2) Now you need to be very clear on your objective function and constraints. But you are all over the map on what you are trying to minimize/maximize and what are the constraints. So if you can restate these from the concisely I may be able to help. You given a few examples, but they are not clear or contradict each other.

I think the objective is
minimize X: where x is the number of visits
subject to: some unclear constraints on partial pallets.

Here is some of my confusion. Going back to my last question.


you need 10 for Prod03 and the stock is

PROD03 BIN01 1 1
PROD03 BIN02 20 2
PROD03 BIN03 42 2

You pull from bin1 and then 2. Which is more trips and the same amount of partial pallets as pulling everything from bin2.
There is either some constraints that makes me pull from the partial pallet in bin1 first, or there is some objective to reduce partial pallets.
What is the constraint/objective to make me choose from bin1 if it increases my trips and creates the same partial pallets?

I have done a fair bit of optimization modeling. And getting the constraints and objective clearly stated is key. Sometimes simple intuitive rules are complex to turn into a function.

I think my fundamental approach for doing this is correct, I just have not correctly interpreted your objective and constraints.

 

[overdraft015]

Ok i will try explain in as much detail and try to interpret a little better.

I guess we have two goals that we need to achieve at the same time. The goal is to visit as few bins as possible (if we can) whilst keeping the part pallets in the upper bins to only 1.

now as there is two goals one must take a priority but which one? this depends on where stock is located and to what qty.

for everything below i am only taking about 1 product

If any qty on the order (i.e. 10 cartons on order so we can use any value from 1-10) = the qty any bin then pick this (if there is the same qty on upper and lower then use priorty, name as the order of allocation), do the same for the remainder (if any).

If there is no qty = to any bin qty or we have a remainder we cant apply the above for then we must use bin priority, name as the order of allocation. yes this means we could visit several bins to make up that qty but we dont create part pallets.

Now all this assumes that nothing less than a full pallet will ever go into an upper bin.

I really hope this makes sense.

Thanks

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

I think this may help a little. a lower bin is rarely a full pallet. a lower bin is mearly a shelf where a few cartons may be put, or if the product is small enough then a lot may be put there. so this will 99% of the time be a part pallet already.

It's not what you know. It's who's on Tek-Tip's

 

[overdraft015]

Slight addition to this bit "..If any qty on the order (i.e. 10 cartons on order so we can use any value from 1-10) = the qty any bin then pick this (if there is the same qty on upper and lower then use priorty, name as the order of allocation), do the same for the remainder (if any)..."

i said any value i.e. 1-10 if 1 bin had the 10 and 1 bin had 9 we would take the bin with 10. if 1 bin had 8 and 1 had 5 we would take the 8.

So take the bin that has the closest to the qty needed aslong as the bin has either the same or less than that qty

I really hope i am not confusing you. i will try draw up a flow chart with the warehouse manager to ensure that we can have an accurate flow of what should happen.

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

I think I got it but you have actually contradicted yourself several times. if you can comment on the following I may have it. Please comment on each numbered item.

1) Are you treating a partially unloaded bin and a partial pallet as the same thing? I assume you can draw full pallets from a upper bin So do the rules apply to creating partial pallets, partial bins, or both. Here is your quote:

"we didnt do it this way then say we had 10 full pallets of 20 cartons each in the upper bins and 5 cartons in the lower bins and i did an order for 15 cartons then by using the visit as few bins a possible senario then i would go to the first upper bin and take all 15 in 1 visit. this leaves me with 5 cartons in the lowers, 5 cartons on an upper pallet and 9 full pallets. if i repeat this order i would then visit another full pallet and take from that creating another part pallet. this would continue untill all the full pallets are now part pallets and i am not using my pallet spaces efficiently."

But what if you took 40 instead of fifteen? Then you would have 8 full pallets and created no partial pallets. Do the rules of drawing from the lower stock first apply even if I do not create a real part pallet.

2) You still did not answer the question. How do you tell if someting is a part pallet?
PROD01 BIN03 10 2

Is that a full pallet of 10 per pallet?
Is that a half pallet of 20 per pallet?

3) Are you planning to track quantity per pallet as I suggested?
EX

I assume you need a product table
tblProduct
productID
productName
unitsPerPallet (number of cartons, cases, sets, etc. per pallet)
other product field


Data example
Product Name UnitSPerPallet
PROD01 20
Prod02 10
Prod03 20

with this simple table I can then do a query to calculate full pallets and partial pallets for a given product in a given bin.

4) If you are not tracking or calculating partial pallets. How do you solve this problem? Assume you have two upper bins with 100 of Prod01
Bin03 100 Pri2
bin04 100 Pri2

If I need to draw 60 from an upper bin I would end up with

Bin03 40 Pri2
Bin04 100 Pri2

On my next order I need to draw 50 from an upper bin. I have no idea that I have drawn from Bin03 already nor do I have a method to determine if I have a partial pallet or not. So what is the correct choice. And how do you expect to determine what choice to make. The algorithm would draw from Bin04 because that is the most economical. But maybe you want to draw from bin03 first then 10 from bin 04 because you drew from bin 3 already.


5) Now lets assume we start with all full pallets in the upper bins, and there is never full pallets in the lower bin. And lets assume the following stock

stock
bin1 prod1 5 pri1
bin2 prod1 3 pri1
bin3 prod1 1 pri1
bin4 prod1 20 pri2
bin5 prod1 20 pri2
bin6 prod1 20 pri2


If I order: prod1 x 4
Do I choose bin 1 (1 trip and leave 1)?
or
Bin2 and bin3 (2 trips and leave none)?

Since bottom bins are partials, am I reducing trips or trying to remove partial pallets?


6) you need 10 from Prod03 and the stock is

PROD03 BIN01 1 1
PROD03 BIN02 20 2
PROD03 BIN03 42 2

You choose 1 from Bin01 and 9 from Bin02. I would have choose 10 from bin02. Both cases create a partial pallet.

Is the rule that you need to empty the bottom bins prior to creating a partial pallet? Or you only want to have one partial pallet? You have said both.

7) same situation as six but the stock is

PROD03 BIN01 1 1
PROD03 BIN02 15 2 Partial pallet
PROD03 BIN03 42 2

where bin02 has partial pallets.

If I need 10 I could do
bin01 x1
bin02 x9
(2 trips no additional partial pallets created)

or
bin 2 x 10
(1 trip no additional partial pallets)

 

[overdraft015]

Sorry for the late reply I have been busy with a project and waiting for the warehouse manager to get back to me. Please see below for the answers to your questions. I hope this is clear, and thank you again for the effort you are putting into this. All my answers should be between ""'s

I think I got it but you have actually contradicted yourself several times. if you can comment on the following I may have it. Please comment on each numbered item.

1) Are you treating a partially unloaded bin and a partial pallet as the same thing? “YES” I assume you can draw full pallets from a upper bin So do the rules apply to creating partial pallets, partial bins, or both. “BOTH” Here is your quote:

"if we didn’t do it this way then say we had 10 full pallets of 20 cartons each in the upper bins and 5 cartons in the lower bins and I did an order for 15 cartons then by using the visit as few bins a possible scenario then I would go to the first upper bin and take all 15 in 1 visit. This leaves me with 5 cartons in the lowers, 5 cartons on an upper pallet and 9 full pallets. if I repeat this order I would then visit another full pallet and take from that creating another part pallet. This would continue until all the full pallets are now part pallets and I am not using my pallet spaces efficiently."

But what if you took 40 instead of fifteen? Then you would have 8 full pallets and created no partial pallets. Do the rules of drawing from the lower stock first apply even if I do not create a real part pallet.

2) You still did not answer the question. How do you tell if something is a part pallet? “We either can set a pallet qty on a product level” or we assume that all pallets in the upper bin are full (with exception to the first pallet in the uppers which could be full or partial”

PROD01 BIN03 10 2

Is that a full pallet of 10 per pallet?
Is that a half pallet of 20 per pallet?

3) Are you planning to track quantity per pallet as I suggested? “Possibly yes, this may differ though depending on the size of the pallet used”
EX

I assume you need a product table
tblProduct
productID
productName
unitsPerPallet (number of cartons, cases, sets, etc. per pallet)
other product field


Data example
Product Name UnitSPerPallet
PROD01 20
Prod02 10
Prod03 20

with this simple table I can then do a query to calculate full pallets and partial pallets for a given product in a given bin.

4) If you are not tracking or calculating partial pallets. How do you solve this problem? Assume you have two upper bins with 100 of Prod01

Bin03 100 Pri2
bin04 100 Pri2

If I need to draw 60 from an upper bin I would end up with

Bin03 40 Pri2
Bin04 100 Pri2

On my next order I need to draw 50 from an upper bin. I have no idea that I have drawn from Bin03 already nor do I have a method to determine if I have a partial pallet or not. So what is the correct choice. And how do you expect to determine what choice to make. The algorithm would draw from Bin04 because that is the most economical. But maybe you want to draw from bin03 first then 10 from bin 04 because you drew from bin 3 already. “I would draw the 40 from bin 03 first because I cannot take all of bin04 and leave this empty, then 10 from bin04” the only reason is because bin03 is the first bin (in name order) they have the same priority and I cannot clear any bin in 1 go.”


5) Now lets assume we start with all full pallets in the upper bins, and there is never full pallets in the lower bin. And lets assume the following stock

stock
bin1 prod1 5 pri1
bin2 prod1 3 pri1
bin3 prod1 1 pri1
bin4 prod1 20 pri2
bin5 prod1 20 pri2
bin6 prod1 20 pri2


If I order: prod1 x 4
Do I choose bin 1 (1 trip and leave 1)? “YES”
or
Bin2 and bin3 (2 trips and leave none)? “NO”

Since bottom bins are partials, am I reducing trips or trying to remove partial pallets? “you would go to the first bin in this case as we should apply the same logic as the uppers bins”


6) you need 10 from Prod03 and the stock is

PROD03 BIN01 1 1
PROD03 BIN02 20 2
PROD03 BIN03 42 2

You choose 1 from Bin01 and 9 from Bin02. I would have choose 10 from bin02. Both cases create a partial pallet.

Is the rule that you need to empty the bottom bins prior to creating a partial pallet? Or you only want to have one partial pallet? You have said both. “because you cannot clear a bin in full you then would use the priority / bin order to pick. So you would choose 1 from bin01 and 9 from bin02”

7) same situation as six but the stock is

PROD03 BIN01 1 1
PROD03 BIN02 15 2 Partial pallet
PROD03 BIN03 42 2

where bin02 has partial pallets.

If I need 10 I could do
bin01 x1
bin02 x9
(2 trips no additional partial pallets created)

or
bin 2 x 10
(1 trip no additional partial pallets)

“For the above again because you cannot clear a bin in full you would choose 1 from bin01 and 9 from bin02”

Thank you

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

Just about got it. Just one more (I think). I was not sure if the "whole Bin" concept matters for the bottom row since they are all partial bins to start with? Here is the stock.

Bin1 X 5 lower
Bin2 X 10 lower
Bin3 X 15 lower
Bin4 X 40 lower
Bin5 x 50 lower
bin6 x 100 upper

If you need 45. Do I:

1) Bin 5 X 40 (1 trip but not clearing a bin and leaving 5)
or

2)Bin4 X 40 (take the largest bin first in entirety)
Bin1 X 5 (then the next bin in entirety)

 

[overdraft015]

Thanks Majp

i think the second option would fit better from what they are doing

It's not what you know. It's who's on Tek-Tip's

 

[MajP]

http://www.4shared.com/file/108371558/91e8d73/Stock_Allocation_V1.html

The db above provides the complete solution.

I implemented the solution based on your example and rules. However, so that you know this does not necessarily provide an optimum solution. For example if the only solutions that exist result in a partial bin, there may be several possibilities. The best possibility (fewer trips) could be achieved by first creating the partial bin, then drawing the remainders. With your rules the best decisions is made at each step, which may not lead to the overall best decision. So you will get a good answer, but not always the best answer. This is known as a heuristic solution, and it is often the approach for very complex optimizations.

Here is the general approach of how this works. I built a few queries

qryEqualsRequiredLower: Returns the lower bins where the bin qty = qty desired. Sorted by bin order.

qryEqualsRequiredUpper: Returns the upper bins where the bin qty = qty desired. Sorted by bin order.

qryLessThanRequired: Returns the bins that contains less than the required amount. sorted by priority then by bin order. example: you need 30 it returns any bin with 29 or less.

qryMoreThanRequiredLower. Returns the lower bins where the the bin qty is more than the required qty. Sorted by bin order


qryMoreThanRequiredUpper. Returns the upper bins where the the bin qty is more than the required qty. Sorted by bin order

So basically I built functions that return the bin qty and bin id for the first record from these queries. These are the functions

Public Function getProductID() As String
  getProductID = glblProductID
End Function

Public Function getNeededQuantity()
  getNeededQuantity = glblNeededQuantity
End Function

Public Function getBinID(strQry As String) As String
  getBinID = Nz(DLookup("Bin", strQry), "None")
End Function

Public Function getBinQty(strQry As String) As Integer
  getBinQty = Nz(DLookup("Qty", strQry), 0)
End Function

Public Function getAvailable()
  getAvailable = Nz(DLookup("TotalQty", "qryTotalAvailable"), 0)
End Function

Public Function canFillOrder() As Boolean
  If getAvailable() >= glblNeededQuantity Then
    canFillOrder = True
  End If
End Function

So this is basically the logic and the backbone of the code. If the logic is a little off then the queries can be easily modified to support different rule sets

Public Sub processOrderItem(productID As String, OrderID As String, qty As Integer)
  Dim binID As String
  Dim binQty As Integer
  glblNeededQuantity = qty
  glblProductID = productID
  If Not canFillOrder Then
    MsgBox "Can not fill order.  Requires more than available."
  Else
    Do Until glblNeededQuantity = 0
      'check to see if you have an exact match in the lower bin equal to required
      If Not getBinID("qryEqualsRequiredLower") = "None" Then
         Call makeSelection(productID, OrderID, "qryEqualsRequiredLower")
         GoTo endIfLbl
      'check to see if you have an exact match in the upper bins equal to required
      ElseIf Not getBinID("qryEqualsRequiredUpper") = "None" Then
         Call makeSelection(productID, OrderID, "qryEqualsRequiredUpper")
         GoTo endIfLbl
      'Check a the lower bins for a bin that has more than you need and pick the first one
      ElseIf Not getBinID("qryMoreThanRequiredLower") = "None" Then
         Call makeSelection(productID, OrderID, "qryMoreThanRequiredLower")
         GoTo endIfLbl
      'check the both bins to find the largest that is less than required
      ElseIf Not getBinID("qryLessThanRequired") = "None" Then
         Call makeSelection(productID, OrderID, "qryLessThanRequired")
         GoTo endIfLbl
      'Check the upper bins and empty the highest priority first.
      ElseIf Not getBinID("qryMoreThanRequiredUpper") = "None" Then
         Call makeSelection(productID, OrderID, "qryMoreThanRequiredUpper")
         GoTo endIfLbl
      End If
endIfLbl:
    Loop
  End If
End Sub

1 you pass the required product, and needed qty.
2 You first check if you can fill the order
3.Then you enter the loop and continue until you fill the order. You check each query in order. If you meet a possibility, you update the stock and fill the pick table. If you have a remainder then you start back at the top of the loop checking each query.

See if you can exactly fill the order from a single bottom bin
see if you can exactly fill an order from a singl top bin
See if one of the lower bins has more than you need
find a bin that has less than you need and grab from the bottom first
find an upper bin that has more than required and always grab from the lowest bin order


these procedures update the stock and pick table

Public Sub updateStock(productID As String, binID As String, qty As Integer)
    Dim strSql As String
    strSql = "UPDATE Stock SET Stock.Qty = " & qty & " WHERE Bin = '" & binID & "' AND Product = '" & productID & "'"
    Debug.Print strSql
    CurrentDb.Execute strSql
End Sub

Public Sub updatePick(Product As String, Bin As String, qty As Integer, OrderID As String)
    Dim strSql As String
    strSql = "INSERT INTO Pick ( Product, Bin, Qty, [Order] ) "
    strSql = strSql & "Values ('" & Product & "', '" & Bin & "', " & qty & ", '" & OrderID & "')"
    Debug.Print strSql
    CurrentDb.Execute strSql
End Sub

This procedure reads the Orders table and starts processing all of the orders

Public Sub processOrders()
  Dim rs As DAO.Recordset
  Dim productID As String
  Dim OrderID As String
  Dim qty As Integer
  CurrentDb.Execute "delQryClearStock"
  CurrentDb.Execute "delQryClearPicks"
  CurrentDb.Execute "appQryReloadStock"
  Set rs = CurrentDb.OpenRecordset("qryOrders")
  Do While Not rs.EOF
    productID = rs!Product
    OrderID = rs!Order
    qty = rs!qty
    processOrderItem productID, OrderID, qty
    rs.MoveNext
  Loop
End Sub

The form demonstrates the code and shows how the orders fill. There is additional code than what is provided that ties these main concepts together. I think the code is very well designed to allow you to modify the logic without much rework of the code.

 

[overdraft015]

Thanks Majp, i have downloaded and read your post. it looks very comprehensive.

I will run some testing and play around and see the results and i will get back to you on this.

(also my offer for any glassware still remains )

Thank you once again.

It's not what you know. It's who's on Tek-Tip's