Extract Records that equal item total 1

[benderulz]

First off, I am using Access 2010. I have a table [selecting_locations] that lists item number, warehouse location, and qty. For example
item_nbr location qty
123456 P123A 1
123456 P321A 5
123456 P123B 10
654321 C456D 2
654321 C654D 1
654321 C345A 6

and another table [order] that shows the qty of each item that needs to be selected. For example,
item_nbr qty
123456 1
654321 3

These tables could contain thousands of rows depending on the order. How can I extract only the records from [selecting_locations] that add up to the qty in the [order] table? For example, only the following records would be returned.

item_nbr location qty
123456 P123A 1
654321 C456D 2
654321 C654D 1

I'm assuming that I need to loop through the recordset, but honestly have no idea where to start. If you could point me in the right direction, it would be greatly appreciated.

 

[MajP]

Are there any rules on which records to pick first?
Largest quantity?
Smallest quantity?
Geographic location? (warehouses close to each other)
Fewest records?

What happens if there is not an exact combination of records to fill order exactly?
Go over?
Partial order from a warehouse?

example you need 7
available: 6,4,4,2,2

example you need 7
available: 8,6,4,4


Is there something you want to minimize or maximize?
Fill order with minimum different warehouses?

In other words if you were telling someone to fill the order and they had lots of different ways to do it, or no single way that would be exact, how would you tell them to do it?

 

[Andrzejek]

I would try something like this:

[tt]SELECT S.item_nbr, S.location, S.qty
FROM selecting_locations S, order O
WHERE S.item_nbr = O.item_nbr
AND S.qty = O.qty[/tt]

Have fun.

---- Andy

 

[MajP]

That would only return records where you have an exact match, that is not going to return a group of records totaling to the order amount.

 

[benderulz]

I have the records in the selecting_locations sorted by quantity in ascending order, in hopes to grab those records first. I know that I would mostly likely not return exact quantity matches, so I would need the loop to stop as soon as the selecting_qty is equal to or greater than order_qty and move on to the next item number.

In short, I would send the person to the smaller quantity locations first, and then pull the remaining quantity from a larger quantity location.

example you need 7
available: 6,4,[highlight #FCE94F]4,2,2[/highlight] - Pulling only 3 from the 4 qty

example you need 7
available: 8,6,[highlight #FCE94F]4,4[/highlight] - Pulling only 3 from one of the locations

 

[MajP]

In short, I would send the person to the smaller quantity locations first, and then pull the remaining quantity from a larger quantity location.

Buisiness wise that does not seem to make sense, but if that is your policy then it is relatively easy. ( I would have assumed you would want to fill with the fewest amounts of different locations, and there would be some penalty for breaking an order)

The next question is what do you want to do with the results? I assume you want some kind of staging table to hold the identified orders for each required qty. What do you want to do with the partial records? What do you want to do if you cannot fill a complete order?

Like you said I would loop the required order table, then loop the available location table. Read the values and do an insert query into my staging table.
I would probably fill a table like this (non normal but holds results)

[tt]
item_nbr RequiredQty  Location  Available   Used   Partial
123456      1         P123A        1         1     NO
654321      3         C456D        2         2     NO
654321      3         C654D        1         2     NO
333333      7         ABDCE        4         4     NO
333333      7         ABDCE        4         3     YES
777777      100 
[/tt]

The partial fields shows where you take less than waht is available from a location. 77777 is an order that cannot be filled (required qty > total available), not sure what you want to do there.

 

[benderulz]

MajP, you definitely understand what I'm trying to do, but I don't understand how to get the loop to stop once it reaches the appropriate quantity needed and move onto the next item. Below is the code I have so far, that builds a table similar to the one above. I believe the highlighted section is what I need to change, but don't really know how. I'm fairly new to vba and what I know I have picked up from forums and a few books, so the code below may not be the cleanest, but it seems to partially accomplish my goal.



Set db = CurrentDb
Set rs = db.OpenRecordset("cleanup_selecting")
With rs
[highlight #FCE94F]Do While Not .EOF[/highlight]
sqls = "INSERT INTO holding (item_nbr,qty_needed,priority,location,location_qty)"
sqls2 = "Values ('" & rs!item_nbr & "','" & rs!qty_needed & "','" & rs!priority & "','" & rs!location & "','" & rs!location_qty & "');"
sqls3 = sqls + sqls2

CurrentDb.Execute sqls3

.MoveNext
Loop
.Close
End With

 

[MajP]

Here is some rough code (untested). One thing your insert query treats everything as strings, I assume that qty_Needed is long same as location_qty

Public Sub FillOrders()
  Dim rsOrders As dao.Recordset
  Dim rsLocations As dao.Recordset
  Dim strSql As String
  Dim intRequired As Integer
  Dim totalRequired As Integer
  Dim totalAvailable As Long
  Dim runningSum As Long
  Dim amountSelected As Integer
  Dim enoughAvailable As Boolean
  Dim orderItmNumber As String
  Dim itmNumber As String
  Dim location As String
  Dim locationQty As Integer

  Set rsOrders = CurrentDb.OpenRecordset("Order", dbOpenDynaset)

  
  'clear holding table
  strSql = "Delete * from Holding"
  CurrentDb.Execute strSql
  Do While Not rsOrders.EOF
    'get the item number and required qty from the orders table
    orderItmNumber = rsOrders!item_nbr
    totalRequired = rsOrders!qty
    'get the available amount
    totalAvailable = DCount("qty", "selecting_Locations", "item_nbr = " & orderItmNumber)
    enoughAvailable = (totalAvailable >= totalRequired)
    'if not enough then just put a single record
    If Not enoughAvailable Then
      strSql = "INSERT INTO holding (item_nbr,qty_needed"
      strSql = strSql & "Values ('" & rs!item_nbr & "'," & rs!qty_needed & ")"
      CurrentDb.Execute strSql
    Else
      'loop location for that item and sorted correctly
       strSql = "Select * from selecting_locations where item_nbr = ' " & orderItmNumber & "'order by item_nbr, qty"
       Set rsLocations = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
       Do
         locationQty = rsLocations!qty
         location = rsLocations!location
         'Either take all available or take the remainder needed
         If runningSum + locationQty <= totalRequired Then
           amountSelected = locationQty
         Else
           amountSelected = totalRequired - runningSum
         End If
         runningSum = runningSum + amountSelected
         strSql = "INSERT INTO holding (item_nbr,qty_needed,priority,location,location_qty)"
         strSql = strSql & "Values ('" & orderItmNumber & "'," & totalRequired & ",'" & rsLocation!priority & "','" & location & "'," & amountSelected & ")"
         CurrentDb.Execute strSql
       Loop Until runningSum = totalRequired Or rsLocations.EOF
    End If
      runningSum = 0
   rsOrder.MoveNext
  Loop

Also don't you want to record partial location orders. (How are you tracking if you only pick 4 of the 5 available). I would add that to the holding table.

 

[benderulz]

Tweaked a couple things and it ran perfectly!! Thank you so much for your help MaJP

Dim rsOrders As DAO.Recordset
Dim rsLocations As DAO.Recordset
Dim strSql As String
Dim intRequired As Integer
Dim totalRequired As Integer
Dim totalAvailable As Long
Dim runningSum As Long
Dim amountSelected As Integer
Dim enoughAvailable As Boolean
Dim orderItmNumber As String
Dim itmNumber As String
Dim location As String
Dim locationQty As Integer

Set rsOrders = CurrentDb.OpenRecordset("Order", dbOpenDynaset)


'clear holding table
strSql = "Delete * from Holding"
CurrentDb.Execute strSql
Do While Not rsOrders.EOF
'get the item number and required qty from the orders table
orderItmNumber = rsOrders!item_nbr
totalRequired = rsOrders!qty
'get the available amount
[highlight #FCE94F]totalAvailable = DSum("qty", "selecting_Locations", "[item_nbr] = '" & orderItmNumber & "'")[/highlight]
enoughAvailable = (totalAvailable >= totalRequired)
'if not enough then just put a single record
If Not enoughAvailable Then
strSql = "INSERT INTO holding (item_nbr,qty_needed"
strSql = strSql & "Values ('" & rs!item_nbr & "'," & rs!qty_needed & ")"
CurrentDb.Execute strSql
Else
'loop location for that item and sorted correctly
strSql = "Select * from selecting_locations where item_nbr = '" & orderItmNumber & "' order by item_nbr, qty"
Set rsLocations = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
Do
locationQty = rsLocations!qty
location = rsLocations!location
'Either take all available or take the remainder needed
If runningSum + locationQty <= totalRequired Then
amountSelected = locationQty
Else
amountSelected = totalRequired - runningSum
End If
runningSum = runningSum + amountSelected
strSql = "INSERT INTO holding (item_nbr,qty_needed,location,location_qty)"
strSql = strSql & "Values ('" & orderItmNumber & "'," & totalRequired & ",'" & location & "'," & amountSelected & ")"
CurrentDb.Execute strSql
[highlight #FCE94F]rsLocations.MoveNext[/highlight]
Loop Until runningSum = totalRequired Or rsLocations.EOF
End If
runningSum = 0
rsOrders.MoveNext
Loop