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 example Table02 with 2 fields the first
with combinations of the numbers of Table01 and the 2nd field 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

I have find a code in VB but how can I use it in Access mdb file.


1. Dim Table2() As String
2. Dim TmpDou As Double
3. Dim i() As Integer 'hoping you table has less than 32000 elements
4. Dim j As Integer
5. Dim n As Integer
6. Dim k As Long 'this will be for counting the combinations
7. Dim Boo1 As Boolean
8. TmpDou = UBound(Table1) - LBound(Table1) + 1 'The number of elements in table1
9. TmpDou = 2 ^ TmpDou - 1 'the number of combinations excluding the empty one
10. ReDim Table2(1 To TmpDou, 1 To 2) 'this will be our working space
11. ReDim i(1 To TmpDou) 'this will be for counting
12. 'In case your indexes doesnt start at cero:
13.
14. n = 1
15. Do
16. If n > UBound(Table1) - LBound(Table1) + 1 Then Exit Do
17. ReDim i(1 To n)
18. For j = 0 To n - 1
19. i(j + 1) = LBound(Table1) + j
20. Next
21. Do
22. k = k + 1
23. TmpDou = 0
24. For j = 1 To n
25. Table2(k, 1) = Table2(k, 1) & Table1(i(j)) & ", "
26. TmpDou = TmpDou + Table1(i(j))
27. Next
28. Table2(k, 1) = Left(Table2(k, 1), Len(Table2(k, 1)) - 2)
29. Table2(k, 2) = TmpDou
30. i(n) = i(n) + 1
31. If i(n) > UBound(Table1) Then
32. j = n - 1
33. i(n) = UBound(Table1)
34. Do
35. If j = 0 Then
36. Boo1 = True
37. Exit Do
38. End If
39. If i(j) < i(j + 1) - 1 Then
40. i(j) = i(j) + 1
41. i(j + 1) = i(j) + 1
42. Exit Do
43. Else
44. j = j - 1
45. End If
46. Loop
47. End If
48. If Boo1 = True Then
49. n = n + 1
50. Boo1 = False
51. Exit Do
52. End If
53.
54. Loop
55. Loop

[MMcCarthy]

Although this can be done in theory it is very bad practice. Storing multiple values in one field has no logical purpose in a database. Can you explain why you are trying to do this?