Many to Many All Possible Combinations
This can be automated to some extent. First, though, take both your [Clients] and [Plans] tables and rename their primary fields to be exactly what you put in your sample. To keep your existing client names intact, insert a new field to the right of the primary field, copy the true client names in there, then change the primary to the new names. Here’s my dummy [Clients] table as an example:
If you have a small client list, making the dummy names should be fairly easy. For a large list, you can shortcut this step by adding an Autonumber field somewhere in the table, then change the primary field to a formula:
"Client " & Autonumber
After the primary field fills with names, change the type to Single Line Text. All the formulated names will be kept, and you can nix the Autonumber field. Repeat this process on your [Plans] table to make dummy plan names: Plan 1, Plan 2, etc.
Now moving to your [Clients-Plans] table, make three fields. The first will be an Autonumber field (with a matching name). The other two will be formula fields, and the formulas you choose will be driven by how many plans you have. In my example, I have five plans, but you’ll change each 5 in the following formulas based on your plan count.
First is a {Clients} formula field:
"Client " & ROUNDUP(Autonumber / 5, 0)
Next is a {Plans} formula field:
"Plan " & (MOD(Autonumber-1, 5) + 1)
What you end up with is this (note: all three of the following screenshots are truncated):
Now convert the two formula fields to links pointing to their respective tables, which gives you this:
Now go back to the [Clients] and [Plans] tables, copy the true names back into the primary fields, and nix the temp name fields, and your [Clients-Plans] table looks like this:
