Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    17

    Unanswered: Find best combination in MS Access VBA

    I need to find best combination using Loop to count "NumerOfSheets" To achieve smallest possible number from among the largest. Taking into account additional blocks to allocate.

    My table before running code looks like
    Code:
     ID Oder Quantity Blocks NumberOfSheets
      1  A    350      2      
      2  B    200      1      
      3  C    100      1
    At the beginning I was using code (I had no additional blocks):
    Code:
    SELECT ID, Order, Quantity, Blocks, Round(Quantity / Blocks) As NumberOfSheets
    FROM tbl1;
    It worked! But now I have new field in my main form "Forms!frmGlowny!FreeBlocks" Where I keep number of blocks to allocate (additional blocks which I can allocate in the column "Blocks"). This filed is count by another code. What is important now, this is positive integer (usually no more than 20). I need find best way to allocate my free blocks. What is best way? - The largest number from "NumerOfSheets" should be as small as possible. Suppose that this example Forms!frmGlowny!FreeBlocks = 1 (so it's very simple example). So Let's find where I should allocate my 1 free block (I need do it by hand, because I don't have a code:/).

    Combination 1
    Code:
    ID Oder Quantity Blocks NumberOfSheets
    1  A    350      3       117  
    2  B    200      1       200
    3  C    100      1       100
    Combination 2
    Code:
    ID Oder Quantity Blocks NumberOfSheets
    1  A    350      2       175  
    2  B    200      2       100
    3  C    100      1       100
    Combination 3
    Code:
    ID Oder Quantity Blocks NumberOfSheets
    1  A    350      2       175  
    2  B    200      1       200
    3  C    100      2       50
    The smallest possible number from among the largest is in the combination No. 2 (because the largest = 175 so it is smallest from all largest numbers of combinations), so now I know that my 1 free block should be added to B order to column "Block". It's very simple example because I have only A;B;C oders and 1 block to allocate. But When I will have e.g orders: A;B;C;D;E;F;G;H and 14 blocks to allocate count by hand will be terrible:/ Please guys, help me. Any solution what I found on the Internet, is about defined number of rows (orders in my case) * before running code I always know Order;Quantity;Block(before add additional blocks).

  2. #2
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    radek,

    I think we're both overthinking this a little bit.

    If you can start with everyone getting one block;
    Then you can assign the remaining blocks one-at-a-time to the most deserving (highest NumberOfSheets).
    Do this until you run out of blocks.

    Code:
    Public Sub MapBlocks(intBlocks As Integer)
    Dim BlocksLeft As Integer
    Dim rst As DAO.Recordset
    '
    ' Set All Blocks To 1
    '
    CurrentDb.Execute "Update YourTable " & _
                      "Set BLocks = 1, " & _
                      "    NumberOfSheets = Quantity/Blocks"
    '
    ' Allocate the next "free" block to the largest remaing NumberOfSheets
    '
    BlocksLeft = intBlocks
    While BlocksLeft > 0
       Set rst = CurrentDb.Openrecordset("Select * from YourTable Order by NumberOfSheets Desc")
       rst.Edit
       rst!Blocks = rst!Blocks + 1
       rst!NumberOfSheets = rst!Quantity / rst!Blocks
       rst.Update
       rst.Close
       set rst = Nothing
       BlocksLeft = BlocksLeft - 1
       Wend
    End Sub
    hth,
    Wayne

  3. #3
    Join Date
    Apr 2013
    Posts
    17
    Quote Originally Posted by WayCal View Post
    radek,
    I think we're both overthinking this a little bit.
    I think all the time about this:/.

    First of all. Thank you for insight.
    Please tell me, what number in my example "intBlocks" is? Because I think this code doesn't work:/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •