# Find best combination in MS Access VBA (1 Viewer)

##### Registered User.
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:
Dim recIn As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl1;"
Set recIn = CurrentDb.OpenRecordset(strSQL)
While Not recIn.EOF
recIn.Edit
recIn!NumberOfSheets = Round((recIn!Quantity / recIn!Block), 1)
recIn.Update
recIn.MoveNext
Wend
recIn.Close`
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).

#### MarkK

##### bit cruncher
See this code?
Code:
Dim recIn As Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl1;"
Set recIn = CurrentDb.OpenRecordset(strSQL)
While Not recIn.EOF
recIn.Edit
recIn!NumberOfSheets = Round((recIn!Quantity / recIn!Block), 1)
recIn.Update
recIn.MoveNext
Wend
recIn.Close
You can replace that whole block with one line . . .
Code:
currentdb.execute "UPDATE tbl1 SET Sheets = Quantity / Blocks", dbfailonerror
In fact it is so easy to perform that operation, that it is not recommended that you actually do it in the table. What you should do is write a query like this . . .
Code:
SELECT ID, Order, Qty, Blocks, Qty / Blocks As Sheet
FROM tbl1
. . . so you never store the number of Sheets, you always calculate it. So that addresses "part 1" of your post.

In part 2 how are those combinations created? The ID values are the same, but the data is different. Are those combinations in different tables?

##### Registered User.
In part 2 how are those combinations created? The ID values are the same, but the data is different. Are those combinations in different tables
I only want to show the problem. Of course I can create form and write every combination, what I do manually, but It's not solution for my problem. In some cases the number of combinations is so large that you can not do that manually. So in this example I have a code in "Order" double click event. I change manually "blocks" then run code and note The smallest possible number...and I do it again with another combinations of blocks:/

#### MarkK

##### bit cruncher
One idea I have to calculate a "best-fit" is sum all your quantities, and then divide by total block count, so: 850 / 5. You get 170, which, if it could be done, would be your "best-fit" number of sheets overall.

Now, what scenario minimizes the absolute error?

Code:
350 / 2 = 175, error  5
200 / 2 = 100, error 70
100 / 1 = 100, error 70
====
total error 145
Code:
350 / 2 = 175, error  5
200 / 1 = 200, error 30
100 / 2 = 50, error 120
====
total error 155
So that looks like a calculable method of finding your best fit scenario.

And coding the solution will be very interesting. Keep me posted, because I have some ideas about that too, but I'm out of time for now,

##### Registered User.
...sum all your quantities, and then divide by total block count, so: 850 / 5. ...
sum of all my quantities is 650 not 850. So it doesn't work:/

#### MarkK

##### bit cruncher
Of course it still works. Just do all the math with the correct numbers.

650 / 5 is 130

Code:
350 / 2 = 175, error 45
200 / 2 = 100, error 30
100 / 1 = 100, error 30
====
total error 105

350 / 2 = 175, error 45
200 / 1 = 200, error 70
100 / 2 = 50, error 80
====
total error 195

350 / 3 = 117, error 13
200 / 1 = 200, error 70
100 / 1 = 100, error 30
====
total error 113
The principle is sound, my arithmetic bites.

Do you need help coding it?

##### Registered User.
Of course it still works
Hmm so You claim that, where the total error is the smallest, that woul be best scenario. Am I right? I've checked it on few examples and it is truth. But for coding I think it is the same problem as "The smallest possible number from among the largest".
Do you need help coding it?
It would be great! Any solution what I found on the Internet, is about defined number of rows:/:/:/:/. So I don't know is it possible to coding this solution?

#### MarkK

##### bit cruncher
There are a few ways I think we could approach this, but first of all, I don't have time. So can you make a database with the basic data, so one table with some sample data we can use, and then maybe make the one function that sums the quantities in the table. If you can do that, then I would take a look, and write a bit of code that I think would get us closer. We also need a form to specify how many blocks we have, right? So maybe make a form too, and base your total quantity on there, and a way to specify the number of blocks. How does that sound?

You need to
1) make a database with data
2) make a form
3) teach the form how to sum the quantities
4) make a way for us to specify the block count
5) make a button that says "GO"

then post the database

##### Registered User.
You need to
1) make a database with data
2) make a form
3) teach the form how to sum the quantities
4) make a way for us to specify the block count
5) make a button that says "GO"
then post the database

Of course, I will prepare what you need + some examples. Give me time for tomorrow

#### Attachments

• base.zip
89.7 KB · Views: 190

#### MarkK

##### bit cruncher
Here's a solution. It is not totally automatic yet, but it allocates one block with each button click to the record with the worst error. See what you think. You can see that if we run the button clicks in a loop until the free blocks are used up, then we can automate the solution.

For now, click the button once for each additional block to allocate, and you can see what happens.

#### Attachments

• base.zip
56.6 KB · Views: 188

##### Registered User.
Here's a solution. It is not totally automatic yet, but it allocates one block with each button click to the record with the worst error. See what you think. You can see that if we run the button clicks in a loop until the free blocks are used up, then we can automate the solution.

For now, click the button once for each additional block to allocate, and you can see what happens.
All this time I was checking the code. It works great! Could You help me automate the solution? I saw modules but things like cEngine and Array are too complicated for me to understand.

#### MarkK

##### bit cruncher
Well, you can just loop the button click, right? But what is your workflow? Presumably you have to integrate this into your system, where your data is, but I can't do that automation for you.

This Db just automates the button click

#### Attachments

• base.zip
43.5 KB · Views: 197

#### gemma-the-husky

##### Super Moderator
Staff member
optimising stuff like this comes under a general heading of linear programming. It is actually a very complex area.

dealing with a single variable is a simple case, I imagine, as you will get the most efficient use by first selecting the outputs with the smallest waste. The bigger problem is when you have multiple variables that are used in different proportions.

##### Registered User.
Well, you can just loop the button click, right? But what is your workflow? Presumably you have to integrate this into your system, where your data is, but I can't do that automation for you.

This Db just automates the button click
Thank You very much, it really help me with my work, now it's much easier

Replies
25
Views
787
Replies
8
Views
556
Replies
26
Views
1,152
Replies
10
Views
634
Replies
16
Views
391