Finding groups of numbers from multiple records

[rjordan]

I have a database with around 5083 records and I am trying to find the three most common numbers called in any one record such as 5 22 and 39 has been called together 50 times

Here is the structure # 1 through 5 is a number between 1 and 39 but never duplicated in same record

Record 1 |draw#|Date|#1|#2|#3|#4|#5|
Record 2 |draw#|Date|#1|#2|#3|#4|#5|
Record 3 |draw#|Date|#1|#2|#3|#4|#5|
Record 4 |draw#|Date|#1|#2|#3|#4|#5|
Record 5 |draw#|Date|#1|#2|#3|#4|#5|
Record 6 |draw#|Date|#1|#2|#3|#4|#5|
Record 7 |draw#|Date|#1|#2|#3|#4|#5|
and so on for 5083 records

Thanks
Randy

 

[KeithG]

Can you exaplain more in depth? Where are the numbers stored in #2-#5? It doesn't look like you have your db normlized so I believe you are going to run into problems.

 

[rjordan]

The numbers are stored in #1 through #5
Each record is a draw of 5 numbers between 1 and 39
Ican post a smaller version of the database if needed say maybe just 5 records

Thanks
Randy

 

[Mike375]

I am not sure but I think he has the 1 to 5 just to show numbers. I think his actual db would be

Record 1 |draw#|Date|31|23|2|16|7|

I take this......such as 5 22 and 39 has been called together 50 times....to mean there were 50 records with a 5 22 and 39

Which field the numbers are in does not matter. It looks like the equivalent of finding the most common Lotto draw

 

[rjordan]

I am not sure but I think he has the 1 to 5 just to show numbers. I think his actual db would be

Record 1 |draw#|Date|31|23|2|16|7|

I take this......such as 5 22 and 39 has been called together 50 times....to mean there were 50 records with a 5 22 and 39

Which field the numbers are in does not matter. It looks like the equivalent of finding the most common Lotto draw

That is axactley right.... can it be done

Thanks
Randy

 

[Mike375]

Randy,

I have a feeling that is a maths problem.

I was going to cheat and ring the Lotto office and ask them how they worked out the most common draws.....but that is not the same as you are wnating the most common 3 numbers from 5 balls

Here is a link to a big Excel forum. In my exerience high end Excel users can be Actuaries and similar. It is a very interesting problem.

http://www.mrexcel.com/forum/index.php

 

[Cosmos75]

Here's a sample file of what I think you are attempting to do.

First I created a query to combine 3 numbers for each record as a string. For each combination of number positions (1-2-3, 1-2-4, 1-2-5, ... , 3-4-5), there is such a query.

Also, in each query described above, I use IIF statements to find the MAX and MIN number (and the middle number), to create a string where the numbers are ordered from smallest-to-largest from left-to-right.

Then a union query is used to join all of the data into one table. There are two queries that count the number of occurances for each number combination regardless of which position each of the 3 numbers are from.

qryOccuranceCount: Count of each un-ordered string.
qryOccuranceCount_Ranked: Count of each ordered string.

Not sure I explained it clearly but I hope the sample file makes things clearer. There's probably a better way to do this but this is what I could come up with in short order.

 

[rjordan]

Here's a sample file of what I think you are attempting to do.

First I created a query to combine 3 numbers for each record as a string. For each combination of number positions (1-2-3, 1-2-4, 1-2-5, ... , 3-4-5), there is such a query.

Also, in each query described above, I use IIF statements to find the MAX and MIN number (and the middle number), to create a string where the numbers are ordered from smallest-to-largest from left-to-right.

Then a union query is used to join all of the data into one table. There are two queries that count the number of occurances for each number combination regardless of which position each of the 3 numbers are from.

qryOccuranceCount: Count of each un-ordered string.
qryOccuranceCount_Ranked: Count of each ordered string.

Not sure I explained it clearly but I hope the sample file makes things clearer. There's probably a better way to do this but this is what I could come up with in short order.

Thanks for the reply and the sample. I am curious if the sample you sent was supposed to have the queries you mentioned, I don't see them I only see the tables.

Thanks
Randy

 

[Mike375]

I got 'em all. About a dozen queries. Real neat stuff he made.

 

[rjordan]

I got 'em all. About a dozen queries. Real neat stuff he made.

I wonder if it is because I am using access 2007

Randy

 

[Mike375]

Maybe can you see it if I attach it for you.

 

[Cosmos75]

I am using Access XP/2002. The file is in Access 2000 format.

I wonder if it is because I am using access 2007

Don't have Access 2007 so I'm not sure if that's the problem. Did a quick search on the MS Office website and it looks like Access 2007 can work with the Access 2000 file format.

From http://office.microsoft.com/en-us/access/HA100210701033.aspx?pid=CH100645711033

In some cases, you may prefer to create files in an earlier Access format. In Office Access 2007, you have the option of creating files in either the Access 2000 format or the Access 2002-2003 format (both with the extension ".mdb"). Then, when you create a new database file, the resulting file is created in the earlier Access format and can be shared with others who use that version of Access. This can be helpful if you have upgraded to Office Access 2007 but you share files with others who have not.

Maybe someone else using Access 2007 can tell us if they have the same issue as rjordan.

Real neat stuff he made.

 

[rjordan]

I am using Access XP/2002. The file is in Access 2000 format.


Don't have Access 2007 so I'm not sure if that's the problem. Did a quick search on the MS Office website and it looks like Access 2007 can work with the Access 2000 file format.

From http://office.microsoft.com/en-us/access/HA100210701033.aspx?pid=CH100645711033

Maybe someone else using Access 2007 can tell us if they have the same issue as rjordan.

It was my error I had the wrong view set.

Thanks
Randy