all posible combinations

[cliff]

have the following programme.module for generating all possiible
combination of in a group of 3 items and it works like this

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim stocknames() As String
Dim i As Long
Dim j As Long
Dim k As Long
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "select stockname from stocknames order by stockname", ,
adOpenForwardOnly, adLockReadOnly

i = 0
ReDim stocknames(0)
Do While Not rs.EOF
ReDim Preserve stocknames(i)
stocknames(i) = rs.Fields("stockname")
i = i + 1
rs.MoveNext
Loop
rs.CLOSE
rs.Open "stockpermutations", , adOpenDynamic, adLockOptimistic
For i = 0 To UBound(stocknames) - 2
For j = i + 1 To UBound(stocknames) - 1
For k = j + 1 To UBound(stocknames)
If i <> j And j <> k And i <> k Then
rs.AddNew
rs.Fields("first") = stocknames(i)
rs.Fields("second") = stocknames(j)
rs.Fields("third") = stocknames(k)
rs.Update
End If
Next k
Next j
Next i
rs.CLOSE
End Sub

result is something like this :-

first second third
12 23 35
12 23 36
12 35 36
23 35 36

but I want my output is something like this
sr first
1 12
1 23
1 35
2 12
2 23
2 36
3 23
3 35
3 36
In words I want autonumber for a each new groups and display values in
one column as sr i.e., autonumber seperate the combinations. Please help me


thanks

clifford

 

[Bob Barrows]

have the following programme.module for generating all possiible
combination of in a group of 3 items and it works like this

result is something like this :-

first second third
12 23 35
12 23 36
12 35 36
23 35 36

but I want my output is something like this
sr first
1 12
1 23
1 35
2 12
2 23
2 36
3 23
3 35
3 36
In words I want autonumber for a each new groups and display values in
one column as sr i.e., autonumber seperate the combinations. Please
help me

union query:
select 1 as sr, [first] from stockpermutations
union all
select 2, [second] from stockpermutations
union all
select 3, [third] from stockpermutations

 

[Jeff Boyce]

Cliff

Another approach would be to generate the "possible combinations" on the
fly, using a query. If you add two tables (or one table twice) to a query,
but don't join them, add one field from each, you get a collection of all
possible combinations. This is also known as a "cartesian product".

That way, you wouldn't need another table to store those combinations, and
you can always (on the fly) re-generate the collection, even after the
original list(s) change.


--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Daryl S]

Cliff -

If your stockpermutations file has an autonumber field, then you don't need
to change your code in creating the data to get the field populated. If you
don't have an autonumber field there, just add it. Then you just need a
query to display what you want. Say your autonumber field is called asr.
Then your query would look like this:

SELECT asr, "asr" AS RecType, sr from stockpermutations
UNION ALL
Select asr, "First" AS RecType, [First] from stockpermutations
UNION ALL
Select asr, "Second" AS RecType, [Second] from stockpermutations
UNION ALL
Select asr, "Third" AS RecType, [Third] from stockpermutations
ORDER BY sr, RecType;

Note that the order by works only because the autonumber field and the three
field names are alphabetically in order, so use a autonumber fieldname that
starts before "First".