Query for all possible permutations

[Adsmit83]

Hi all,
I work tagging iguanas with unique combinations of coloured beads for field identification. I have a table with 13 unique values corresponding to these bead colours (for example Db is dark blue, B is black, R is red, etc.). When an iguana is given a bead tag the order/number of the beads is unique to that animal (for example: an iguana which is given the bead code Db,B,R in that order will be the only one which ever receives this tag).

The tags can be up to 5 beads long.

Bead colours can be repeated (for example, R,R,R is a valid code, as is R,B,R).

I would like a query which gives me all possible permutations of these 13 unique values so I can see which tags have not been already used, and to print a report of those tags so researchers in the field know which ones to use (I'll query out tags which have be used already). I'm aware that this will give me some 300,000 results - I'll have the report print only the first page of the available results.

For clarity, the values are as below:

B

Db

G

Go

Lg

O

P

Pi

Pu

R

Si

W

Y

Many thanks!

Adam

 

[petertenthije]

For this answer, I am going to assume the following:

The table with possible bead colours is called: TBL_Bead_colours
The header inside table TBL_Bead_colours with the colour codes is called:Colour_options
There is no table called TBL_Possible_bead_combinations.

My solution will create a new table called TBL_Possible_bead_combinations and add every possible combination of 1 through 5 beads in there. If you already have a table with that name, then this table will be permanently overwritten.

My solution consists of five queries. The first query makes the table, and fills it with all single bead options.
The second query will fill the table with all double-bead options.
The third query will fill the table with all triple-bead options.
And so on...


Query 1

SELECT TBL_Bead_colours.Colour_options INTO TBL_Possible_Bead_combinations
FROM TBL_Bead_colours;

Query 2

INSERT INTO TBL_Possible_Bead_combinations ( Colour_options )
SELECT [TBL_Bead_colours].[Colour_options] & "-" & [TBL_Bead_colours_1].[Colour_options] AS Expr1
FROM TBL_Bead_colours, TBL_Bead_colours AS TBL_Bead_colours_1;

Query 3

INSERT INTO TBL_Possible_Bead_combinations ( Colour_options )
SELECT [TBL_Bead_colours].[Colour_options] & "-" & [TBL_Bead_colours_1].[Colour_options] & "-" & [TBL_Bead_colours_2].[Colour_options] AS Expr1
FROM TBL_Bead_colours, TBL_Bead_colours AS TBL_Bead_colours_1, TBL_Bead_colours AS TBL_Bead_colours_2;

Query 4

INSERT INTO TBL_Possible_Bead_combinations ( Colour_options )
SELECT [TBL_Bead_colours].[Colour_options] & "-" & [TBL_Bead_colours_1].[Colour_options] & "-" & [TBL_Bead_colours_2].[Colour_options] & "-" & [TBL_Bead_colours_3].[Colour_options] AS Expr1
FROM TBL_Bead_colours, TBL_Bead_colours AS TBL_Bead_colours_1, TBL_Bead_colours AS TBL_Bead_colours_2, TBL_Bead_colours AS TBL_Bead_colours_3;

Query 5

INSERT INTO TBL_Possible_Bead_combinations ( Colour_options )
SELECT [TBL_Bead_colours].[Colour_options] & "-" & [TBL_Bead_colours_1].[Colour_options] & "-" & [TBL_Bead_colours_2].[Colour_options] & "-" & [TBL_Bead_colours_3].[Colour_options] & "-" & [TBL_Bead_colours_4].[Colour_options] AS Expr1
FROM TBL_Bead_colours, TBL_Bead_colours AS TBL_Bead_colours_1, TBL_Bead_colours AS TBL_Bead_colours_2, TBL_Bead_colours AS TBL_Bead_colours_3, TBL_Bead_colours AS TBL_Bead_colours_4;

13 x single bead
169 x two combinations
2.197 x three combinations
28.561 x four combinations
371.293 x five combinations
402.233 combinations in total

When printing your report, I would strongly advice printing a different page per person. Otherwise you'll still end up with duplicate entries.

 

[petertenthije]

One last bit of advice.

If at all possible, ALWAYS use the same of your 13 colours as a starting colour.

This way should a string of beads fall of the iguana, then you know how to read the string. Whether it be left to right, or right to left. Also, if someone makes a photo with a mobile phone it will also help, because a lot of mobile phones make photos in mirror view.

If you always use (for instance) B as a starting point, you will have 22.620 different combinations assuming starting point is always the same colour, followed by up to four other beads.



late edit to add:
and avoid using the starting colour as a end colour, otherwise you will still end up with potential left-right or right-left questions.

 

[Micron]

If that works, the table should have a field that flags a combo as having been used. That could be a simple yes/no field (since a date field would be of no value) or it could be the pk value for the record of the animal it was used on. Then you pick a record that is not flagged. Rather than present thousands of possibilities, just pick one record at random and have them use that. Or pick the first in the table that is not flagged. Too much bother to allow users to pick their own combinations.

 

[jackd]

Adam,

Thanks either a lot of iguanas, or more specifically, a lot of properties of many iguanas.
An interesting project, but how practical is this as a means of identifying animals in the field?

There was a post a few years ago.
All possible word combinations from letters .. ?

 

[Micron]

I liked that solution so much that I just had to play with it. Unfortunately that means things are not getting done.
Thinking that the table really needs a field that can flag the combinations used, I added a field "IguanaIDfk" which is what I'd have. This field would have the pk value of the animal id as a foreign key (fk) to be able to link to the animal has that combination.

Sub MakeIgaunaTable()
Dim db As DAO.Database
Dim sql As String

On Error GoTo errHandler

Set db = CurrentDb
sql = "SELECT tblColours.ColourCode, Null AS IguanaIDfk INTO tblCombinations FROM tblColours"
db.Execute sql, dbFailOnError
''Debug.Print sql

sql = "INSERT INTO tblCombinations (ColourCode, IguanaIDfk)" _
& " SELECT [tblColours].[ColourCode] & '-' & [tblColours_1].[ColourCode] AS Expr1, Null AS IguanaIDfk" _
& " FROM tblColours, tblColours AS tblColours_1"
db.Execute sql, dbFailOnError

sql = "INSERT INTO tblCombinations (ColourCode, IguanaIDfk)" _
& " SELECT [tblColours].[ColourCode] & '-' & [tblColours_1].[ColourCode] & '-' & [tblColours_2].[ColourCode] AS Expr1," _
& " Null AS IguanaIDfk FROM tblColours, tblColours AS tblColours_1, tblColours AS tblColours_2"
db.Execute sql, dbFailOnError

sql = "INSERT INTO tblCombinations (ColourCode, IguanaIDfk) SELECT [tblColours].[ColourCode] & '-' & [tblColours_1].[ColourCode]" _
& " & '-' & [tblColours_2].[ColourCode] & '-' & [tblColours_3].[ColourCode] AS Expr1, Null AS IguanaIDfk" _
& " FROM tblColours, tblColours AS tblColours_1, tblColours AS tblColours_2, tblColours AS tblColours_3"
db.Execute sql, dbFailOnError

sql = "INSERT INTO tblCombinations (ColourCode, IguanaIDfk) SELECT [tblColours].[ColourCode] & '-' & [tblColours_1].[ColourCode]" _
& " & '-' & [tblColours_2].[ColourCode] & '-' & [tblColours_3].[ColourCode] & '-' & [tblColours_4].[ColourCode] AS Expr1," _
& " Null AS IguanaIDfk FROM tblColours, tblColours AS tblColours_1, tblColours AS tblColours_2, tblColours AS tblColours_3, tblColours AS tblColours_4"
db.Execute sql, dbFailOnError

Application.RefreshDatabaseWindow

exitHere:
Set db = Nothing
Exit Sub

errHandler:
Select Case Err.Number
     Case 3010 'table already exists
          If MsgBox("Table already exists. Delete and re-create?", vbYesNo + 256) = vbYes Then
               DoCmd.DeleteObject acTable, "tblCombinations"
               Resume
          Else
               Resume exitHere
          End If
     Case Else
          MsgBox "Error " & Err.Number & ": " & Err.Description
          Resume exitHere
     End Select
     
End Sub

 

[JEC]

Run this code on en empty sheet for all permuations

Dim ar, sq, p As Long

Sub Perm()
 Dim y As Long
 ar = Array("B", "Db", "G", "Go", "Lg", "O", "P", "Pi", "Pu", "R", "Si", "W", "Y")
 p = 3
   
 st = (UBound(ar) - LBound(ar) + 1)
 ReDim sq(1 To (st ^ p) + (st ^ 4) + (st ^ 5), 1 To 1)
     
 For p = 3 To 5
    Gen_Permutations 1, "", y
 Next
   
 Sheets(1).Range("A2").Resize(UBound(sq)) = sq
End Sub

Sub Gen_Permutations(x As Long, xStr As String, y As Long)
 Dim i As Long
 For i = LBound(ar) To UBound(ar)
    If x = p Then
        y = y + 1
        sq(y, 1) = xStr & ar(i)
    Else
        Gen_Permutations x + 1, xStr & ar(i) & ", ", y
    End If
 Next i
End Sub

 

[JEC]

I had a small adaption (does not differ in output). Could not change the previous code anymore..

Dim ar, sq, p As Long

Sub Perm()
 Dim y As Long
 ar = Array("B", "Db", "G", "Go", "Lg", "O", "P", "Pi", "Pu", "R", "Si", "W", "Y")
  
 st = (UBound(ar) - LBound(ar) + 1)
 ReDim sq(1 To (st ^ 3) + (st ^ 4) + (st ^ 5), 1 To 1)
    
 For p = 3 To 5
    Gen_Permutations 1, "", y
 Next
  
 Sheets(1).Range("A2").Resize(UBound(sq)) = sq
End Sub

Sub Gen_Permutations(x As Long, xStr As String, y As Long)
 Dim i As Long
 For i = LBound(ar) To UBound(ar)
    If x = p Then
        y = y + 1
        sq(y, 1) = xStr & ar(i)
    Else
        Gen_Permutations x + 1, xStr & ar(i) & ", ", y
    End If
 Next i
End Sub

 

[Fluff]

@JEC not sure if you realised, but this is in the Access forum.

 

[JEC]

Lol, I didn’t
Well, now he has it for Excel too haha