MATHS: Combinations of N by K

  • Thread starter Thread starter Savvoulidis Iordanis
  • Start date Start date
S

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?
 
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 said:
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
Click to expand...


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
 
Duane said:
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));
Click to expand...


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
 
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));
Click to expand...
 
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
 
Good catch Marsh. Jus change the couple Numbers in the from to tblNums.

--
Duane Hookom
MS Access MVP


Marshall Barton said:
Duane said:
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));
Click to expand...


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
Click to expand...
 
Savvoulidis said:
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?
Click to expand...


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
 
Duane said:
Good catch Marsh. Jus change the couple Numbers in the from to tblNums.
Click to expand...


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