If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
all posible combinations
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 |
#2
|
|||
|
|||
all posible combinations
cliff wrote:
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 -- HTH, Bob Barrows |
#3
|
|||
|
|||
all posible combinations
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. "cliff" wrote in message ... 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 |
#4
|
|||
|
|||
all posible combinations
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". -- Daryl S "cliff" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|