445,705 Members | 1,795 Online
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

# Number Combination

 P: 4 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 Sep 2 '07 #1