# Permutation Table

We use Python's handy itertools.permutations() function to populate a local Access table with unique permutation values.

Permutations are distinct orderings of a set of items.

Permutations have all sorts of applications in the worlds of mathematics and computing.  One recent example is my brute force attempt at solving the Five-House Logic Puzzle.  Brute forcing this sort of puzzle requires us to test all possible permutations of a set of items.

Permutations have many potential applications in business software:

• Staff Scheduling
• Product Configurations
• Data Analysis

## Calculating Permutation Counts

The number of unique permutations for a set of n items can be computed as a factorial: n!.

For example:

1. 1! = 1
2. 2! = 2
3. 3! = 6
4. 4! = 24
5. 5! = 120
6. 6! = 720
7. 7! = 5,040
8. 8! = 40,320
9. 9! = 362,880

The simplest way to represent a set of unique permutations is to use numbers to represent the items and sort them in lexicographic order.

## Generating Permutations

There are many algorithms for generating permutations, several of which are described in the Wikipedia article on permutations.

There is nothing built into VBA to generate permutations.  You would have to implement one of the many algorithms within VBA to generate these values.  I started down that road myself.  When I did not immediately figure it out on my own, I pivoted to a different approach.

I settled on a three-part solution:

1. Use Python's itertools.permutations() method to generate permutations
2. Save the permutations into text files based on corresponding item count
3. Load the values from those text files into a local Permutation table

## The Python Script

It's been many years since I've written much Python, so I had ChatGPT build the script to my specifications.

``````import itertools

def write_permutations_to_file(file_name, elements):
with open(file_name, 'w') as file:
for perm in itertools.permutations(elements):
line = ' '.join(str(elem) for elem in perm) + '\n'
file.write(line)

if __name__ == "__main__":
elements = [1, 2, 3, 4, 5, 6, 7, 8, 9]

for i in range(1, len(elements) + 1):
file_name = f"permutations_output_{i}.txt"
write_permutations_to_file(file_name, elements[:i])
print(f"Permutations of length {i} written to {file_name}")``````

The script is short and sweet.

• It creates 9 files named:
`permutations_output_1.txt`
`permutations_output_2.txt`
`...`
`permutations_output_9.txt`
• In each file, it creates the permutations for the number of elements embedded in the file name.  For example, `permutations_output_3.txt` has the following contents:
`1 2 3`
`1 3 2`
`2 1 3`
`2 3 1`
`3 1 2`
`3 2 1`
• The files are created in the same folder from where the script is run.

## The Access Table

I created an Access table with 12 fields:

• PermutationID: Autonumber primary key
• ElementCount: The number of elements in the current permutation
• Sequence: The position of the current permutation within the lexicographic order
• Item1 - Item9: Nine fields that hold the permutation values

Here's a sample of what the touble looks like after it has been populated:

Having fields named Item1, Item2, Item3, etc. breaks a cardinal rule of good normalized database design.

However, I think this is one of those rare situations where it makes sense to denormalize our structure.  The above approach will greatly simplify how we end up using this table.

One typical downside to the above approach is that it doesn't scale well if you want to support more than x fields.  In this case, I don't think it would be practical to support more than 9 fields, anyway, considering the number of records we would need for 10 items: `10! = 3,628,800`.

By all means, though, if you need those records, feel free to adjust the table design, Python script, and VBA code to include them in the table.

### The SQL CREATE TABLE Script

If you are interested, here's the SQL script that will build the Permutation table:

``````CREATE TABLE [Permutation] (
[PermutationID] AUTOINCREMENT CONSTRAINT [PrimaryKey] PRIMARY KEY UNIQUE NOT NULL,
[ElementCount] BYTE ,
[Sequence] LONG ,
[Item1] BYTE ,
[Item2] BYTE ,
[Item3] BYTE ,
[Item4] BYTE ,
[Item5] BYTE ,
[Item6] BYTE ,
[Item7] BYTE ,
[Item8] BYTE ,
[Item9] BYTE
)``````

I also added a table index that I called GroupSort which is a combination of the ElementCount and Sequence fields:

## The VBA Code

Below is the code I used to populate the Permutation table with the values in the Python-generated text files from above.

``````' ----------------------------------------------------------------
' Procedure : PopulatePermutationTable
' Date      : 8/8/2023
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/permutation-table/
' Purpose   : Loop through each Python-generated permutations
'               text file and use them to populate the local
'               Permutation table.
' ----------------------------------------------------------------
Sub PopulatePermutationTable()
Const foPermText As String = "C:\Path\To\My\Python\Files\Folder"

Dim ElementCount As Byte
For ElementCount = 1 To 9
Debug.Print "ElementCount: "; ElementCount: DoEvents
Dim fpPermText As String
fpPermText = foPermText & "\permutations_output_" & ElementCount & ".txt"
PopulatePermTblByElement ElementCount, fpPermText
Debug.Print
Next ElementCount

End Sub

' ----------------------------------------------------------------
' Procedure : PopulatePermTblByElement
' Date      : 8/8/2023
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/permutation-table/
' Purpose   : Populate the local Permutation table with lines of text
'               from a text file generated via Python's
'               itertools.permutations() method.
' ----------------------------------------------------------------
Sub PopulatePermTblByElement(ElementCount As Byte, fpPermText As String)
' Open the file for reading
Dim FileNum As Integer
FileNum = FreeFile
Open fpPermText For Input As #FileNum

' Create an array to hold each byte value from the file
Dim Items() As String
ReDim Items(1 To ElementCount)

' Open a recordset to append data to the table
Dim db As DAO.Database
Set db = CurrentDb

Dim rsPerm As DAO.Recordset
Set rsPerm = db.OpenRecordset("Permutation", dbOpenTable)

' Read each line and insert into Permutation table
Dim Seq As Long, LineText As String
Do Until EOF(FileNum)
Seq = Seq + 1
Line Input #FileNum, LineText

' Populate the array
Items = Split(LineText, " ")

' Create a new record in the table
With rsPerm
!ElementCount = ElementCount
!Sequence = Seq
Dim i As Byte
For i = 1 To ElementCount
.Fields("Item" & i).Value = CByte(Items(i - 1))
Next i
.Update
End With

'https://nolongerset.com/poor-mans-status-bar/
If Seq Mod 1000 = 0 Then Debug.Print ".";: DoEvents
Loop

' Close the recordset and release objects
rsPerm.Close
Set rsPerm = Nothing
Set db = Nothing

' Close the file
Close #FileNum

End Sub``````

The fully populated table contains 409,113 records:

You can see that ElementCount 9 is fully populated with 362,880 records and the final line of values is in perfect descending order: `9 8 7 6 5 4 3 2 1`.

## Sample Database File

Here's a database file with nothing but the Permutation table in it.

If you want to use the Permutation table but don't want to jump through all the hoops to build it yourself, this link is for you: PermutationTable.zip (5.2 MB).

##### Acknowledgements
• One or more code samples generated with the help of ChatGPT

All original code samples by Mike Wolfe are licensed under CC BY 4.0