lundi 29 juin 2015

Finding Reccurring Number Combinations in Column of Numbers

I have searched and found discussions and solutions to similar problems, but not quite or as complex as I'm trying to figure out.

I have an access table which consists of two columns Draw Number and Number Drawn as shown below. Draw Number is repeated 20 times, to correspond to the 20 numbers that are drawn in each particular draw.

I'm trying to figure a way to determine the most frequent occurring combination of numbers (5 numbers) for all of the draws in each of the 20 number sets. So for instance, 12341 occurs n x, 12342 occurs nx, 12343 occurs n x, etc.

I've created parameter queries which allow me to search for different number combinations from 2 to 10 numbers, and they work OK returning the number of occurrences of a combination of numbers that I input through a simple UI. But the goal is to figure out pragmatically what the optimum combination of numbers.

Hope this makes sense. And by the way, there are 36 million or so rows in the table. The para queries work quite well however; it takes just over a second to return results for each number added. So, query two numbers = 2 second wait, three numbers = 3 second wait, etc.

I've been thinking about a loop of some type but don't know how to get started? Processing time isn't an issue; can take a day if required!

This is written in VBA and has an assortment of queries, temp tables, etc to get the job done.

DRAW NUMBER NUMBER DRAWN
1   1
1   28
1   19
1   3
1   38
1   46
1   43
1   29
1   13
1   22
1   20
1   11
1   50
1   51
1   53
1   54
1   57
1   64
1   76
1   78
2   29
2   14
2   2
2   1
2   35
2   40
2   39
2   30
2   10
2   27
2   21
2   6
2   42
2   50
2   51
2   53
2   54
2   61
2   65
2   69

Aucun commentaire :

Enregistrer un commentaire