goal seek

[Guest]

Hopefully someone out there has a solution to my problem. I appreciate the
time that everyone has spent posting answers to questions. This is my first
source to look when trying to solve an Access question. Thanks Again

Here is my question.

I need to figure out how to determine which number from a set of numbers add
up to a specific dollar amount.

For example. I might have 10 number 1, 4, 7, 10, 25, 37, 45, 114, 150 &
180. I need to find out which numbers combined together add up to say 345.

I need to make this dynamic so that the original number set can vary, and
the goal number can change.

Any ideas? Is Access this best tool for this?

Again, I appreciate any feedback that I can get.

Scott

 

[Daniel Kaseman]

try something like this

Sub FindNumbers()
Dim MyArray as Integer(9)
Dim i as Integer, j as integer
Dim result as integer
For i = 0 to 9
for j = 0 to 9
result = MyArray(i) + MyArray(j)
next j
if result = 180 then exit for
next i

End Sub

 

[James A. Fortune]

Hopefully someone out there has a solution to my problem. I appreciate the
time that everyone has spent posting answers to questions. This is my first
source to look when trying to solve an Access question. Thanks Again

Here is my question.

I need to figure out how to determine which number from a set of numbers add
up to a specific dollar amount.

For example. I might have 10 number 1, 4, 7, 10, 25, 37, 45, 114, 150 &
180. I need to find out which numbers combined together add up to say 345.

I need to make this dynamic so that the original number set can vary, and
the goal number can change.

Any ideas? Is Access this best tool for this?

Again, I appreciate any feedback that I can get.

Scott

This is a very interesting problem. It resembles the problems in
combinatorics such as, "How may ways can change be made for $2.00 using
up to 10 pennies, 5 nickels, 3 dimes, 7 quarters and a dollar bill?"
Your problem is more like "How many ways can change be made for $345
using 1 dollar bill, 1 four-dollar bill, 1 seven-dollar bill, 1 ten, 1
25-dollar bill... The solution in combinatorics involves the concept of
generating functions. I'll take a look at this problem over the next
several days to see if Access can provide a solution up to certain
limits. I will also try to determine if SQL can provide any help. The
variable number of members in the set spices it up. Hopefully, the
number of variables doesn't get much higher than 10. Pure brute force
using VBA is not very elegant so I'll avoid that. I'm thinking of
returning binary strings to show which numbers are used to get the sum.
One brute force method would be to loop from 1 to 2 ^ 10 where the
index is changed to binary and used to check the sum of that
combination. Again, I'm avoiding that. Also, for starters, I'll assume
that a given number in the set doesn't get repeated.

James A. Fortune

 

[James A. Fortune]

This is a very interesting problem. It resembles the problems in
combinatorics such as, "How may ways can change be made for $2.00 using
up to 10 pennies, 5 nickels, 3 dimes, 7 quarters and a dollar bill?"
Your problem is more like "How many ways can change be made for $345
using 1 dollar bill, 1 four-dollar bill, 1 seven-dollar bill, 1 ten, 1
25-dollar bill... The solution in combinatorics involves the concept of
generating functions. I'll take a look at this problem over the next
several days to see if Access can provide a solution up to certain
limits. I will also try to determine if SQL can provide any help. The
variable number of members in the set spices it up. Hopefully, the
number of variables doesn't get much higher than 10. Pure brute force
using VBA is not very elegant so I'll avoid that. I'm thinking of
returning binary strings to show which numbers are used to get the sum.
One brute force method would be to loop from 1 to 2 ^ 10 where the
index is changed to binary and used to check the sum of that
combination. Again, I'm avoiding that. Also, for starters, I'll assume
that a given number in the set doesn't get repeated.

James A. Fortune

I'm still working on this problem. I even tried using partitions to
attack it from the other end. I'll post back when I have something.

James A. Fortune