You may have to use the "brute force" approach and actually count all the permutations for different episodes and different actor combinations.  But with VBA, even brute force can be done quickly because of the speed of computing power.

This is largely a math problem.  As a simple example, an episode with 4 actors, denoted as {1,2,3,4}, has six possible 2-actor combinations: {1,2}, {1,3}, {1,4}, {2,3}, {2,4}, {3,4}.

The math formula is the following.  And this is taught, I believe, in late high-school math classes or early college classes:

If there are N items, the number of combos with k items is:   N! / [k! (N-k)!]

"!" denotes factorial.  For instance, 5!, or 5 factorial, equals 5 x 4 x 3 x 2 x 1 = 120.

So, if an episode has 10 actors, the number of 3-actor combos is:  

     10! / [3! (10-3)!]
   = 10 x 9 x 8 ... x 2 x 1 / (3 x 2 x 1 x 7 x 6 x 5 x 4 x 3 x 2 x 1)
   = 3628800 / (6 x 5040)
   = 120

Using this formula, we can calculate that an episode with 10 actors can have 45 different 2-actor combinations, 120 different 3-actor combos, 210 different 4-actor combos, 252 different 5-actor combos, 210 different 6-actor combos, 120 different 7-actor combos, 45 different 8-actor combos, 10 different 9-actor combos, and, of course, only one 10-actor combo (because there are only 10 actors in the episode).  

Altogether, there are a total of 45+120+210+252+210+120+45+10+1 = 1013 combos for an episode with 10 actors.  Each combo is *one occurrence* of that combo.  You do this exercise for all episodes, count the number of occurrences of every combo, sort the results descendingly, and that's how you find out the combo(s) with the most number of occurrences.

Access doesn't have a factorial function, so you'll have to create your own custom function for it.

If you are familiar with the math above and with VBA, then this should be enough info for you.  If not, I can provide further info later on.

Hi Kevin. Very clear explanation. I think I get it. I will give it a try.

Thanks for taking the time to spell it out for me. --John

I forgot to include the method of actually creating those combinations.  Let's say you have an array of N items:

     Dim arr As Variant
     arr = Array(1, 2, 3, 4, 5, 6, 7, ......)

The VBA code to produce all 2-item, 3-item, and 4-item combos is in the picture below.  Yes, you need to create a separate procedure for each number of item combos in order to simplify the code.  There may be a way do this in one single procedure but it may be complicated.  

My code simply loops through all the items, each loop combining the current item with subsequent ones, until there are no more item left.  It is not terribly ingenious, just another brute-force method.  You need k number of For-Next loops to find all combos with k number of actors.

I use "Debug.Print" to send each combo to the immediate window, for testing purposes.  You can change that to storing the combos in a table.  After finding all combos, run a summation query on the table to count the number of occurrences of each combo.

Thanks, Kevin! Very much appreciated.