display the result

[cliff]

I 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



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 1
first 12
first 23
first 35
sr 2
first 12
first 23
first 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

 

[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".