Number Combination

[sotirios]

I have a Table (Table01)in Access with one number field name Num (double)
I want a routine to create a new table ex.Table02 with 2 fields the first
with combinations and the 2nd with the sum of the combinations

Example:

Table01
NUM
1
2
3

Table02
Field01 Field02
1 1
2 2
3 3
1,2 3
1,3 4
2,3 5
1,2,3 6

Thanks,
Sotiris

[kadghar]

It seems interesting.
i'll give it a try with arrays: lets say your table is called Table1 and is a doubles array. and lets make Table2 an strings one 'cause we are writing numbers and commas:

Dim Table2() As String
Dim TmpDou As Double
Dim i() As Integer 'hoping you table has less than 32000 elements
Dim j As Integer
Dim n As Integer
Dim k As Long 'this will be for counting the combinations
Dim Boo1 As Boolean
TmpDou = UBound(Table1) - LBound(Table1) + 1 'The number of elements in table1
TmpDou = 2 ^ TmpDou - 1  'the number of combinations excluding the empty one
ReDim Table2(1 To TmpDou, 1 To 2) 'this will be our working space
ReDim i(1 To TmpDou)  'this will be for counting
'In case your indexes doesnt start at cero:
 
n = 1
Do
    If n > UBound(Table1) - LBound(Table1) + 1 Then Exit Do
    ReDim i(1 To n)
    For j = 0 To n - 1
        i(j + 1) = LBound(Table1) + j
    Next
    Do
        k = k + 1
        TmpDou = 0
        For j = 1 To n
            Table2(k, 1) = Table2(k, 1) & Table1(i(j)) & ", "
            TmpDou = TmpDou + Table1(i(j))
        Next
        Table2(k, 1) = Left(Table2(k, 1), Len(Table2(k, 1)) - 2)
        Table2(k, 2) = TmpDou
        i(n) = i(n) + 1
        If i(n) > UBound(Table1) Then
            j = n - 1
            i(n) = UBound(Table1)
            Do
                If j = 0 Then
                    Boo1 = True
                    Exit Do
                End If
                If i(j) < i(j + 1) - 1 Then
                    i(j) = i(j) + 1
                    i(j + 1) = i(j) + 1
                    Exit Do
                Else
                    j = j - 1
                End If
            Loop
        End If
        If Boo1 = True Then
            n = n + 1
            Boo1 = False
            Exit Do
        End If
 
    Loop
Loop

I think there's an easier way, but this one works

[sotirios]

Thank you for the reply,
is that possible to create this code in Access database (mdb) file,
with one table with the number for combinations, 2nd table with the
results and a form with the button than will run the code.

Thans Sotiris

[kadghar]

Thank you for the reply,
is that possible to create this code in Access database (mdb) file,
with one table with the number for combinations, 2nd table with the
results and a form with the button than will run the code.

Thans Sotiris

Yeap, its possible, what you need is to transform the arrays into mdb files.
That's a questin that has already been asked here: http://www.thescripts.com/forum/thread553126.html so i think that might be of help.

Just transform the data base into an array, use the code i gave you, and create the answer array, then transform it into an mdb... I think there should be other ways, but this one could be of help.