MATHS: Combinations of N by K

[Savvoulidis Iordanis]

Hi,
I'm looking for code that displays (or sets in an array) all the possible
combinations
(without concern of their order of appearence) of N numbers by K
that is, if we have N=5 numbers (1,2,3,4,5), the possible combinations of
every K=3 of them are:
1,2,3
1,2,4
1,2,5
1,3,4
1,3,5
1,4,5
2,3,4
2,3,5
2,4,5
3,4,5

The math formula for the count of combinations is : N! / ( K! * (N-K)! )
What I want though, is the actual combinations. Has anybody programmed this
kind of code?

 

[Duane Hookom]

You can create a table (tblNums) with a single numeric field (Num) and
values of the numbers 0- max number. Create a query that adds the table 3
times with SQL like:

SELECT tblNums.Num, tblNums_1.Num, tblNums_2.Num
FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2
WHERE (((tblNums.Num) Between 1 And 5
And (tblNums.Num)<>[tblNums_1].[Num]
And (tblNums.Num)<>[tblNums_2].[Num])
AND ((tblNums_1.Num) Between 1 And 5
And (tblNums_1.Num)<>[tblNums_2].[Num])
AND ((tblNums_2.Num) Between 1 And 5));

 

[Marshall Barton]

I'm looking for code that displays (or sets in an array) all the possible
combinations
(without concern of their order of appearence) of N numbers by K
that is, if we have N=5 numbers (1,2,3,4,5), the possible combinations of
every K=3 of them are:
1,2,3
1,2,4
1,2,5
1,3,4
1,3,5
1,4,5
2,3,4
2,3,5
2,4,5
3,4,5

Here's a procedure:

Sub Combin(ByVal result As String, _
ByVal k As Integer, ByVal level As Integer)
Dim i As Integer

If level >= 3 Then
Debug.Print Mid(result, 2), k, level
Else
For i = k To 5
Combin result & "," & i, i + 1, level + 1
Next i
End If
End Sub

Call it with
Combin "", 1, 0

 

[Marshall Barton]

SELECT tblNums.Num, tblNums_1.Num, tblNums_2.Num
FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2
WHERE (((tblNums.Num) Between 1 And 5
And (tblNums.Num)<>[tblNums_1].[Num]
And (tblNums.Num)<>[tblNums_2].[Num])
AND ((tblNums_1.Num) Between 1 And 5
And (tblNums_1.Num)<>[tblNums_2].[Num])
AND ((tblNums_2.Num) Between 1 And 5));

That will generate a lot of duplicates such as 1,2,3 and
3,2,1 etc.

Is what you meant Duane?

SELECT tblNums.Num, tblNums_1.Num, tblNums_2.Num
FROM tblNums, Numbers AS tblNums_1, Numbers AS tblNums_2
WHERE tblNums.Num Between 1 And 5
AND tblNums_1.Num > tblNums.Num And tblNums_1.Num <= 5
AND tblNums_2.Num > tblNums_1.Num And tblNums_2.Num<= 5

 

[John Nurick]

Hi Duane,

A cartesian query like this produces all the combinations if the order
is relevant (60 in this case), but includes results that have the same
numbers in different orders, so isn't what Savvoulidis asked for.

Do you know a way getting the unordered combinations by SQL?

Savvoulidis: if there isn't a SQL solution, try a web search for
something like
vb generate unordered combinations algorithm OR code

You can create a table (tblNums) with a single numeric field (Num) and
values of the numbers 0- max number. Create a query that adds the table 3
times with SQL like:

SELECT tblNums.Num, tblNums_1.Num, tblNums_2.Num
FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2
WHERE (((tblNums.Num) Between 1 And 5
And (tblNums.Num)<>[tblNums_1].[Num]
And (tblNums.Num)<>[tblNums_2].[Num])
AND ((tblNums_1.Num) Between 1 And 5
And (tblNums_1.Num)<>[tblNums_2].[Num])
AND ((tblNums_2.Num) Between 1 And 5));

 

[Savvoulidis Iordanis]

Wow... I think your name suits you. You 're a real marshall!!

When I see code like that, I say to myself 'There is a long way to go...'
Care to explain it a little ? (or maybe convert it so the results go into a
2dim array?

Savvoulidis Iordanis
Greece

 

[Duane Hookom]

Good catch Marsh. Jus change the couple Numbers in the from to tblNums.

--
Duane Hookom
MS Access MVP

SELECT tblNums.Num, tblNums_1.Num, tblNums_2.Num
FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2
WHERE (((tblNums.Num) Between 1 And 5
And (tblNums.Num)<>[tblNums_1].[Num]
And (tblNums.Num)<>[tblNums_2].[Num])
AND ((tblNums_1.Num) Between 1 And 5
And (tblNums_1.Num)<>[tblNums_2].[Num])
AND ((tblNums_2.Num) Between 1 And 5));

That will generate a lot of duplicates such as 1,2,3 and
3,2,1 etc.

Is what you meant Duane?

SELECT tblNums.Num, tblNums_1.Num, tblNums_2.Num
FROM tblNums, Numbers AS tblNums_1, Numbers AS tblNums_2
WHERE tblNums.Num Between 1 And 5
AND tblNums_1.Num > tblNums.Num And tblNums_1.Num <= 5
AND tblNums_2.Num > tblNums_1.Num And tblNums_2.Num<= 5

 

[Marshall Barton]

Wow... I think your name suits you. You 're a real marshall!!

When I see code like that, I say to myself 'There is a long way to go...'
Care to explain it a little ? (or maybe convert it so the results go into a
2dim array?

Basically, it uses a recursive procedure to cycle through
the possible values. If you're not familar with using a
recursive procedure, a text book may be appropriate. In a
nutshell, it's just a procedure that calls itself. It uses
arguments to keep track of where it is in the overall
process and to determine when to not call itself (in this
case, when the third column is filled in).

Here's one for an array. It's more complicated because it
also has to manage the array and propagate already
calculated values to new rows.

Dim aryResults(1000, 1 To 3) As Integer
Dim intRow As Integer

Sub CombinA(ByVal k As Integer, ByVal level As Integer)
Dim i As Integer

If level > 3 Then
For i = 1 To 3 - 1
aryResults(intRow + 1, i) = aryResults(intRow, i)
Next i
intRow = intRow + 1
Exit Sub
Else
For i = k To 5
aryResults(intRow, level) = i
CombinA i + 1, level + 1
Next i
End If
End Sub

Sub Combin()
Dim i As Integer
intRow = 0
Erase aryResults
CombinA 1, 1
End Sub

 

[Marshall Barton]

Good catch Marsh. Jus change the couple Numbers in the from to tblNums.

Dumb Copy/Paste always copies the souce without regard to
destination ;-(