# Thread: How to find all combinations within a constraint Thanks: 0 Likes: 0

1. ## How to find all combinations within a constraint

I am after some code/advice for Access.
I have a list of Order Numbers(Unique) and what cubic metres that each are, I also have a cost benefit per Order.
I would like to run some code to be able to find every combination of Orders but within a given constraint of Cubic size (65Cube) to find the most cost effect mix of Orders in the minimum amount of results (Lowest Container Loads)
So if I had for instance 9x PO's below, in this case due to them all being 20Cube, the minimum you could build would be 3 containers, but which combo would (not duplicating) output the highest cost benefit.
```11111 20Cu £600 22222 20Cu £2,500 33333 20Cu £30,000 44444 20Cu £10,000 55555 20Cu £300 66666 20Cu £23,000 77777 20Cu £50,000 88888 20Cu £100 99999 20Cu £1,000 ```There are 84x Combinations in this case and top three would be:
```333333 666666 777777 222222 444444 999999 111111 555555 888888 ```In the real case, there shall be 1800+ PO's and all of different cube.

2. ## Re: How to find all combinations within a constraint

I don't think you have defined the problem clearly (i.e., what is Lowest Container Load and how do you calculate cost effectiveness). Also your sample solution of the top 3 lists 9 POs, so its not a valid solution. There is really very little that makes sense in having one order provide 50,000 pounds of cost effectiveness for 20 cubic volume and another only 100 pounds of cost effectiveness for the same cubic volume - its as if you have one order for gold and another for iron ore - not a practical problem really (do you need iron or gold - you can't really substitute one for the other).

By the way this really has little to do with Access - generally its a problem you would tackle with Excel solver or some other linear programming model.

3. ## Re: How to find all combinations within a constraint

What you're describing is a perfect example of the Knapsack problem.

https://en.wikipedia.org/wiki/Knapsack_problem

There is an algorithm described in that article, which could be easily adapted to Excel VBA. But as xenou said, it doesn't have much to do with Access.

5. ## Re: How to find all combinations within a constraint

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.