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

 

[JEC]

You will have more than 6000000000 results with 13 values.

 

[JEC]

Correction, I misunderstood

 

[Micron]

Combinations and permutations are usually based on samples taken from a population. You seem to have a population of 13 but want to do sampling 3 different ways. 3 out of 13 where repetition is allowed seems to be 2197 possibilities using Excel PermutationA function. Maybe some sort of Cartesian query will get you the results but any such query that I played with so far doesn't come close to PermutationA(13,3)+PermutationA(13,4+)PermutationA(13,5) = 402,051 (not even sure if that is correct) and the deeper I got the messier it became. If you were dealing with numbers for the sample tables (i.e. 3,4 and 5) you might have a chance, but you're not because each of those numbers represents a set of permutations itself. I'd abandon the idea and show what was already used, not what wasn't. If you stick with it, try looking for info on random permutations.

​

 

[jackd]

Adam,
Your examples show 3 beads and a maximum of 5. Is 3 the minimum number of beads to identify an individual?
So RRRRR is valid.
The colours represent something --perhaps you could tell us what and more.

 

[jackd]

I think micron may have provided this number (my guess using N^^r) since there are repeats

(perms with 3)Take 3 things from 13 +
(perms with 4)Take 4 things from 13 +
(perms with 5)Take 5 things from 13

?(13^^3) +(13^^4) + (13^^5)
402051

 

[petertenthije]

Assuming the beads are listed in Table1, below code can be used to make combinations of 5.

My best advise would be to create 3 queries like this.

One query for combinations of 3, which copies the contents to a new table.
One query for combinations of 4, which adds the contents to the table with combinations of 3.
One query for combinations of 5, which adds the contents to the table with combinations of 3 and 4.

Below code makes combinations of 5

SELECT [Table1].[Bead] & "," & [Table1_1].[Bead] & "," & [Table1_2].[Bead] & "," & [Table1_3].[Bead] & "," & [Table1_4].[Bead] AS Expr1
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3, Table1 AS Table1_4;

 

[Adsmit83]

Thanks everyone! I think maybe I didn't explain the process that well.

The bead tags given to each iguana are basically like ear piercings with small beads threaded in a specific order onto a piece of fishing wire. They consist of a unique combination of the 13 colours, up to 5 beads long but an iguana can have a bead code that is 1, 2, 3, 4, or 5 beads long; so an iguana with the bead code B is marked with a single black bead; an iguana with the bead code BRB is given one black, one red, and one black bead, in that order. The iguana will then be referred to by that code for monitoring purposes permanently (i.e. BRB is effectively that iguana's name forever, and even if in 10 years it dies no other iguana will ever be given that name, and that's extremely important). We do this so that researchers can see an iguana in a tree in the study site and say "Oh, that's BRB; he was captured to be measured last week, so we don't need to recapture him." Or alternatively "That's BRB; he hasn't been measured since he was a juvenile 5 years ago, so we need to capture and measure him."

Because it's extremely important that no two iguanas are ever given the same code, I need a table which lists all possible combinations of tags 1, 2, 3, 4 and 5 beads long, from which I will then query out all code which have been used already. Researchers can then print out the first page or so of those codes, knowing that they can use any of them without risking a repeat tag.

Currently, the system works as Micron suggested - I have a report that is a list of already used tags and all the researchers have to do is choose any bead combination that is not on this list, but apparently that's too complicated for some people, and I'm getting repeated combinations...

 

[jackd]

If you can have 1 to 5 beads, and there are 13 colors and you can repeat, then I suggest

?(13^^1) +(13^^2) +(13^^3) +(13^^4) +(13^^5)
402233

 

[Micron]

Getting the number is one thing. Getting the data values is another. I think it is impossible to do this with tables and queries and will require code. I tend to overthink things but for tables/queries I figure it would be a UNION of Cartesian queries based on
- 1 table with 13 colour values that you'd group singly with each combination table (as created in the next point) but not join on
- 1 table for each set of combinations taking 1 at a time, 2 at a time ... 5 at a time. That's 2379 tables (13, 78, 286, 715, 1287). Already that is a problem and that's before creating any queries. I might be wrong there and it's permutations that would be required, not combinations, but it's a moot point given the number of tables it would take.

I suggest you look for a code solution for Access because I suspect this has been done somewhere. It probably involves generating a list of numbers, then mapping each number value to a colour number. I'm thinking that would only require 1 table with the 13 colours but creating the code from scratch would be daunting (at least for me) but I imagine it's doable.

If users have been duplicating permutations, then they must be assigning combinations without using forms, and that is a no-no. Or the form isn't designed properly so that it doesn't allow duplicates. A DLookup on a proposed combination could prevent dupes and is probably better than a 6 field composite table index.