All Possible combinations - Numbers

[editolis]

Hi,

Need some help with my table.

I Want to calculate the results from my table with all the possible combinations and to put them to another table.

For example:

tbl1
ID | Num
1 | Num1 = 2,15
2 | Num2 = 3,69
3 | Num3 = 4,85
etc

I Want this to the table tbl2:
Num1 | Num2 | Num3 etc | Results
2,15 | Null | Null | res1(Num1*Num2*Num3 etc) = 2,15
2,15 | 3,69 | Null |res2(Num1*Num2*Num3 etc) = 7.93
2,15 | 3,69 | 4,85 |res3(Num1*Num2*Num3 etc) = 38.47
3,69 |4,85 | Null etc...

Grand total (Res1+Res2+Res3 etc) = 655.89

Imagine if you have id 30 how many combinations you have.

Thank you in advance

 

[redneckgeek]

I've noodled over this for a little while. I love to try to find the answer to "the impossible" query. I'll go out on a limb here and say that this can be done with a query. It will have to be done with VB, and even then it won't be easy.

 

[editolis]

A Vip member have already put the database "combinenumbers.zip" for other thread which is very close what i am looking for.
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=18603&d=1188843021

The think is that i want the number of columns the results in columns not in one column with decimel and then i want to multiply them to see the results.

 

[WayneRyan]

It definitely can't be done with a query.

It was interesting on the surface, but after starting in on it I'm losing
interest fast.

The attached DB is pretty close.

Wayne

 

[ajetrumpet]

This is very easy to do EditOlis. If you're interesting in getting what you want, send me a PM.

 

[redneckgeek]

This is very easy to do EditOlis. If you're interesting in getting what you want, send me a PM.

What, you don't want to share with the rest of us?

 

[ajetrumpet]

what, You Don't Want To Share With The Rest Of Us?

nope......

 

[editolis]

It definitely can't be done with a query.

It was interesting on the surface, but after starting in on it I'm losing
interest fast.

The attached DB is pretty close.

Wayne

Dear Wayne

Thank you for your help and your DB.

It is very close what i am looking for.

I Need 2 more thinks from you to add to your DB.

1. I Want not the range but the data(Numbers) from my table to generate results. Let say 10 numbers with decimel.

2. Can you please give me the total at the bottom of the form?

Thank you again and waiting for your reply.

 

[WayneRyan]

Editolis,

I just put the range and # of decimals to make up the test data.
Other than providing the data, it has no real bearing on the
algorithm.

If you already have data, ignore that part and use your table.

I didn't put a grand total but that also shouldn't be that tough
to add in.

hth,
Wayne

 

[gemma-the-husky]

been thinking generally abuot this

to get the overall total, i think you would be best looking at a solution using recursion, rather than trying to use queries etc


this will only give you the grand total though i think

 

[WayneRyan]

Gemma,

When you look at all the possible combinations with something like 20-25
numbers, that'd be a LOT of stack space!

It'd be a very minor code mod to sum up the already established totals for
each row.

BUT, given 30 numbers, averaging about 10, gives a grand total on
the order of:

10 * 15 * 2 ^ 30 !!

What would that number really mean anyway?

If it is really important, I can mod the code in the sample to give a grand
total.

Wayne

 

[Banana]

I could be very dense, but wouldn't cartesian join provide all possible combinations, using three queries that retrieve Num1, Num2, Num3 into a "table". You then can get the sum with another query upon that cartesian join query.

Air-SQL:

SELECT Num1, Num2, Num3 FROM (SELECT Num AS Num1 FROM tblNum WHERE Num="Num1"), (SELECT Num AS Num2 FROM tblNum WHERE Num="Num2"), (SELECT Num AS Num3 FROM tblNum WHERE Num="Num3");

 

[dkinley]

Here's what I came up with ... I am only posting because I doinked around with it and wasn't going to delete the fruits of my labors. =]

If you have a table called "tTableInput" and it has 2 fields (ID, SomeNumber). It dynamically creates a table, "tOutPut" and starts shoving it all in there with an ID field and a Totals field on the end. Since it is dynamic, it will only work as long as the input table is only 2 fields.

Dim MyDb As DAO.Database
Dim tblOutPut As DAO.TableDef
Dim rsInput As DAO.Recordset
Dim rsOutPut As DAO.Recordset
Dim colFieldName As DAO.Field
Dim avarRec As Variant
Dim iRecord As Long
Dim iField As Long
Dim jField As Long
Dim zField As Long
Dim iCompute As Double
 
Set MyDb = CurrentDb()
Set tblOutPut = MyDb.CreateTableDef("tTableOutput")
 
'create table and set table columns
With tblOutPut
    'set autonumber field
    Set colFieldName = .CreateField("pkTableOutputID", dbLong)
    colFieldName.Attributes = dbAutoIncrField + dbFixedField
    .Fields.Append colFieldName
    'create the other fields
    For iRecord = 1 To CurrentDb.TableDefs("tTableInput").RecordCount
         .Fields.Append .CreateField("Num" & CStr(iRecord), dbDouble)
    Next iRecord
    .Fields.Append .CreateField("Total", dbDouble)
End With
 
MyDb.TableDefs.Append tblOutPut
 
Set colFieldName = Nothing
Set tblOutPut = Nothing
Set rsInput = CurrentDb.OpenRecordset("tTableInput", dbOpenSnapshot)
Set rsOutPut = CurrentDb.OpenRecordset("tTableOutput", dbOpenDynaset)
 
'get all the rows and put em in an array
avarRec = rsInput.GetRows(CurrentDb.TableDefs("tTableInput").RecordCount)
zField = 0
 
'update tableoutput with numbers and totals
Do Until zField = rsOutPut.Fields.Count - 2
    With rsOutPut
        For iField = 0 To rsOutPut.Fields.Count - 3
            rsOutPut.AddNew
            iCompute = avarRec(1, iField)
            For jField = zField To iField
                rsOutPut("Num" & CStr(jField + 1)) = avarRec(1, jField)
                If jField < iField Then iCompute = iCompute * avarRec(1, jField)
            Next jField
            rsOutPut(CurrentDb.TableDefs("tTableInput").RecordCount + 1) = iCompute
            If zField <> jField Then rsOutPut.Update
        Next iField
    End With
    zField = zField + 1
Loop
 
Set rsInput = Nothing
Set rsOutPut = Nothing

I just did it from the left working in the columns per the demo in the OP (from left to right). It doesn't line up all the columns fancy like and, well, at least I think it should be working. =] What I didn't do was a grand total.

-dK

EDIT: If you wanted all combinations (n elements taken r at a time), you could reverse the sweep to pick up the other half of the combinations.

 

[WayneRyan]

Great to see all you folks in on this.

Definitely not Cartesian Banana, and recursion is out too!

All combinations/permutations of 30 numbers is a major undertaking.

My sample just aligned the "n" numbers with a binary string of the same
length. By toggling the ones/zeros, you do get all combinations. I never
had the patience or disk space to watch it run at thirty numbers, but with
10/15/20 it's not too bad.

For each iteration, the sample gives the chosen elements as an equation and
the resulting total. Summing the individual totals really didn't occur to me
because I don't know what value the sum of 2^20 rows of calculations
really offers.

It was an interesting exercise, but I really don't want to play anymore.

Wayne