Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-04-2014, 03:01 AM   #1
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
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:
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).

radek225 is offline   Reply With Quote
Old 10-04-2014, 10:05 AM   #2
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-04-2014, 10:45 AM   #3
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Thank you for your advice.
Quote:
Originally Posted by MarkK View 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
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:/

radek225 is offline   Reply With Quote
Old 10-04-2014, 02:46 PM   #4
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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,
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-07-2014, 03:47 AM   #5
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Thank you for your insight
Quote:
Originally Posted by MarkK View Post
...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:/
radek225 is offline   Reply With Quote
Old 10-07-2014, 10:35 AM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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?
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-09-2014, 11:06 PM   #7
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Quote:
Originally Posted by MarkK View Post
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".
Quote:
Originally Posted by MarkK View Post
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?

radek225 is offline   Reply With Quote
Old 10-14-2014, 06:22 PM   #8
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 10-14-2014, 11:53 PM   #9
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Quote:
Originally Posted by MarkK View Post
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 is offline   Reply With Quote
Old 10-17-2014, 05:51 AM   #10
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Quote:
Originally Posted by MarkK View Post
So can you make a database...
Ok, I prepared database.
Attached Files
File Type: zip base.zip (89.7 KB, 49 views)
radek225 is offline   Reply With Quote
Old 11-12-2014, 09:22 AM   #11
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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.
Attached Files
File Type: zip base.zip (56.6 KB, 53 views)
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
Old 11-27-2014, 06:05 AM   #12
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Quote:
Originally Posted by MarkK View Post
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.
radek225 is offline   Reply With Quote
Old 11-27-2014, 08:53 AM   #13
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,761
Thanks: 10
Thanked 1,290 Times in 1,227 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
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
Attached Files
File Type: zip base.zip (43.5 KB, 54 views)
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
radek225 (12-08-2014)
Old 11-27-2014, 10:46 AM   #14
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Find best combination in MS Access VBA

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.
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 12-08-2014, 05:30 AM   #15
radek225
Newly Registered User
 
Join Date: Apr 2013
Posts: 307
Thanks: 75
Thanked 0 Times in 0 Posts
radek225 is on a distinguished road
Re: Find best combination in MS Access VBA

Quote:
Originally Posted by MarkK View Post
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

radek225 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combination Field Problem Access 2010 arama Forms 0 10-01-2011 09:38 AM
problem with combination of word and access using mass correspondence (2 conditions) matejsykora Queries 0 10-16-2010 02:20 AM
access report combination iniohos Reports 4 10-28-2009 05:11 AM
IF OR AND combination scott-atkinson Excel 3 12-28-2007 04:12 AM
Creating a form in MS Access for displaying reports according to the combination chos goels Forms 1 10-06-2004 10:22 AM




All times are GMT -8. The time now is 04:41 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World