# Thread: Find best combination in MS Access VBA

1. Registered User
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. Registered User
Join Date
Dec 2013
Posts
46
Provided Answers: 2
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. Registered User
Join Date
Apr 2013
Posts
17
Originally Posted by WayCal
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
•