MS Access Sum Combination Possibilities?

Can Access query a data set and provide a list of possible sum combinations?

i.e. "Amount" table has 6 amounts stored: 5, 10, 15, 25, 35, 53

I would like query to display all summations where: "sum = 40"

My results would be something like:

1) 15, 25
2) 5, 35

Any direction would be greatly appreciated.

Thanks.

Interesting question. Are your amounts stored as individual records or are they in separate fields within one record? But to answer your question depending on the first answer, you could probably create a query with some If ... Then statements if the numbers are in one record vs 6 records. Also, given the actual sum amount, I can guess that you might end up with more than two array amounts that would add up to what you are seeking. Example the numbers 2 3 5 10 15 would give you two or three or four arrays if the sum you were looking for was 20. If you are looking for a query to possibly compute all those possibilities, I don't think you would be very successful or at least the query would be so complex I don't think the sql can bear to handle that. If you are familar with writing code, an approach to consider would be creating a recordset and the performing a loop through each record and field and evaluate for a true/false condition against the sum you are looking for and I doubt if that is even the best way. Perhaps someone is familar with a piece of code that can do this easier than I described here.

If you come up with a method or solution, I would be quite interested.

lwells

Thanks for the reply! This query would use many different records in a single column table. I agree that an array would have to be produced in nearly all situations. If anyone out there has a simple example with the creation of a record set using if... then... in code, (or another idea that may be easier) I would really appreciate it. Thanks again.

I created a subfunction that works by clicking a button (Command0) on a form to populate another table called AMT2. The initial table is called Amount.

I'm sure that this could be streamlined, but perhaps this will point you in the direction that you need.

Private Sub Command0_Click()
Dim rst As Recordset
Dim rst2 As Recordset
Dim intCounter As Integer
Dim jcounter As Integer
Dim arrCount As Integer
Dim barrCount As Integer
Dim cmd1 As Command
Dim cmd2 As Command
Dim strSQL As String

DoCmd.RunSQL ("DELETE FROM Amt2")
Set rst = New ADODB.Recordset
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT * from Amount"
cmd1.CommandText = strSQL
cmd1.CommandType = adCmdText
cmd1.Execute

Set rst2 = New ADODB.Recordset
Set cmd2 = New ADODB.Command
cmd2.ActiveConnection = CurrentProject.Connection
cmd2.CommandText = strSQL
cmd2.CommandType = adCmdText
cmd2.Execute

rst.Open cmd1
rst2.Open cmd2

For intCounter = 1 To 5
arrCount = rst!Value
rst2.MoveFirst
rst2.Move (intCounter)
For jcounter = (intCounter + 1) To 6
barrCount = rst2!Value
If arrCount + barrCount = 40 Then
DoCmd.RunSQL ("INSERT INTO Amt2 (Val2) Values ('" & arrCount & "," & barrCount & "')")
End If
rst2.MoveNext
Next jcounter
rst.MoveNext
Next intCounter

List1.RowSource = "SELECT Val2 from AMT2 order by Left([val2],InStr(1,[val2],'" & "," & "')-1) desc"

Set cmd1 = Nothing
Set cmd2 = Nothing
Set rst = Nothing
Set rst2 = Nothing

End Sub

ywflagg,

How would you incorporate more than two variables in the array. For example if the numbers were 2,3,5 and the sum was 10, how would you find all three? The example code posted would not find this combination.

This is more for conversation, rather than actual code problem...but the source code is interesting. The beta table using DAO recordsets, that I have been playing with still doesn't capture more than three arrays correctly and I am finding this an interesting challenge.

If you have some thoughts or ideas, maybe you would share.

Thanks,
lwells

lwells,

When I replied to the posting I made a few assumptions. I probably should not have done that...however I assumed
1) The records in the recordset table were unique - no duplicates
2) There were only 2 possible values that would return the sum value.

It appears that you took this question farther than I did, so I am sorry, but I don't have any additional insight for the array issue that you are experiencing.

ywflagg,

Thanks for the reply, like I said I don't have any problems, but was just looking at some various code samples to perform unique calculations like szym9341 wanted.

lwells

If you were to come up with a function, could that function be run in the query? Or would it be better to create a recordset and run the function on that? This is assuming you had a lot of numbers to choose from and wanted to do it as efficiently as possible.

That would be a fun function to create.

Hi MrBullwinkle

I agree with you, this is very interesting and fun to experiment with. I am still playing around with the function, not sure if I would ever really need it, but just the same it might come in handy somewhere down the road for who knows what reason, except that szym9341 had a need in their application. The biggest obsticle is creating a variable length array based on the possible length of the recordset and what to do with duplicate numbers. At this point the algorithm is a bit messy and not very efficient that I am creating, but who knows, someone reading this might throw their ideas in the mix and eventually I will come up with something that will work properly based on their suggestions.

Anyway, if I ever come up with something that works correctly (and efficiently), I will post it here.

lwells

Thanks for all of the input and code! I will have to spend some time with what you started and see whether I will be able to add to it to fit the need of possible duplicates and more than two results. Thanks again!