Find best combination in MS Access VBA

radek225 · Oct 4, 2014

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 · Oct 4, 2014

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?

radek225 · Oct 4, 2014

Thank you for your advice.

MarkK said:
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 · Oct 4, 2014

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,

radek225 · Oct 7, 2014

Thank you for your insight

MarkK said:
...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 · Oct 7, 2014

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?

radek225 · Oct 10, 2014

MarkK said:
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".

MarkK said:
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 · Oct 15, 2014

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

radek225 · Oct 15, 2014

MarkK said:
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

radek225 · Oct 17, 2014

MarkK said:
So can you make a database...

Ok, I prepared database.

MarkK · Nov 12, 2014

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.

radek225 · Nov 27, 2014

MarkK said:
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 · Nov 27, 2014

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

gemma-the-husky · Nov 27, 2014

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.

radek225 · Dec 8, 2014

MarkK said:
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

Find best combination in MS Access VBA

radek225

Registered User.

MarkK

bit cruncher

radek225

Registered User.

MarkK

bit cruncher

radek225

Registered User.

MarkK

bit cruncher

radek225

Registered User.

MarkK

bit cruncher

radek225

Registered User.

radek225

Registered User.

Attachments

MarkK

bit cruncher

Attachments

radek225

Registered User.

MarkK

bit cruncher

Attachments

gemma-the-husky

Super Moderator

radek225

Registered User.

Similar threads

Users who are viewing this thread