Find Lotttery Most Drawn Numbers

[Guest]

Hello,

I'm trying to create a database that will find the most often numbers drawn
over a period of time.

Something along this format.

Numbers Drawn Week One: 01 02 03 04 05 06
Numbers Drawn Week Two: 01 07 08 09 10 11
Numbers Drawn Week Three: 01 07 12 13 14 15

Results: 01 07 00 00 00 00

I've created the tables for each number drawn but
cannot come up with a query to find this result.
Possibly I am approaching this in the wrong way.

Any assistance would be greatly appreciated.

Thanks in advance.

Dan........!!!!!

 

[Ken Snell \(MVP\)]

Did you build your table this way (which is a properly normalized table
structure, with one record for each combination of week and number drawn,
thus six records for each week):

tblNumbersDrawn
WeekID (composite primary key with NumberDrawn)
NumberDrawn (composite primary key with WeekID)

If you did, the query is very simple:

SELECT NumberDrawn, Count([NumberDrawn])
FROM tblNumbersDrawn
GROUP BY NumberDrawn
ORDER BY Count([NumberDrawn]) DESC;



If you built your table this way (which is a non-normalized table structure,
with one record for each week):

tblNumbersDrawn
PK_ID (primary key)
WeekID
NumberDrawn1
NumberDrawn2
NumberDrawn3
NumberDrawn4
NumberDrawn5
NumberDrawn6

Then you must use a Union query to combine all the data (make it look like a
normalized table), save the Union query, and then use a second query to get
the results.

qryUnion (create and save this query)
SELECT T1.WeekID, T1.NumberDrawn1 AS NumberDrawn
FROM tblNumbersDrawn AS T1
UNION ALL
SELECT T2.WeekID, T2.NumberDrawn2
FROM tblNumbersDrawn AS T2
UNION ALL
SELECT T3.WeekID, T3.NumberDrawn3
FROM tblNumbersDrawn AS T3
UNION ALL
SELECT T4.WeekID, T4.NumberDrawn4
FROM tblNumbersDrawn AS T4
UNION ALL
SELECT T5.WeekID, T5.NumberDrawn5
FROM tblNumbersDrawn AS T5
UNION ALL
SELECT T6.WeekID, T6.NumberDrawn6
FROM tblNumbersDrawn AS T6;

Then create this query to give you the desired results:

SELECT NumberDrawn, Count([NumberDrawn])
FROM qryUnion
GROUP BY NumberDrawn
ORDER BY Count([NumberDrawn]) DESC;

 

[Guest]

Hi

Is the order drawn really important as they are always listed in ascending
order?

I would have a table like...
tblDraws
DrawID
DrawDate
NumberDrawn

So, for each draw date there will be 6 rows.

To find the most frequent numbers, use something like...

select top 6 NumberDrawn, Count(*)
from tblDraws
group by NumberDrawn
order by count(*) desc

Note: With the few lines of draws you gave as examples all numbers are
returned because many of the numbers are tied on 1 selection. With more draws
entered this won't happen so much but can when numbers are tied - you will
need to specify how you decide between a tie and extend the query to apply
that.

Hope this helps

Andy Hull

 

[John W. Vinson]

I'm trying to create a database that will find the most often numbers drawn
over a period of time.

Don't waste your time. In a fair lottery, the probability that any number will
be drawn today is *exactly the same*, whether it's been drawn in every one of
the past ten lotteries or never drawn in the entire history of the lottery.
And in an unfair lottery, the house always wins anyway.

John W. Vinson [MVP]

 

[Guest]

Did you build your table this way (which is a properly normalized table
structure, with one record for each combination of week and number drawn,
thus six records for each week):

tblNumbersDrawn
WeekID (composite primary key with NumberDrawn)
NumberDrawn (composite primary key with WeekID)

If you did, the query is very simple:

SELECT NumberDrawn, Count([NumberDrawn])
FROM tblNumbersDrawn
GROUP BY NumberDrawn
ORDER BY Count([NumberDrawn]) DESC;

I sure appreciate all the help...and it now works just fine.....Now the
part of figuring out how to have Access automatically update the
weekly entries from the Net...<If possible>.
This is the first time I've asked for help on here...and I sure am
impressed with all the helpful and timely responses. And I thank each and
every one of you..!! Usually I just beat my head against the keyboard until
I figure it out...lol. Though I did take Microsoft Database Applications at
Montana Tech,
some things they just don't teach.

Thanks Again..!!!

 

[Ken Snell \(MVP\)]

I sure appreciate all the help...and it now works just fine.....Now the
part of figuring out how to have Access automatically update the
weekly entries from the Net...<If possible>.

If you're asking how to do this, I am not able to provide an answer because
I've not done such things with ACCESS and don't have experience with it. I
suggest that you post a new thread with a topic concerning the gathering of
data from internet with ACCESS.