Here's a Union ? for the MVP's

  • Thread starter Thread starter noodnutt
  • Start date Start date
N

noodnutt

G'day ppl.

Have been asked if I can put a lotto DB together.

this lotto consists of 6 drawn No's & 2 Sup's which will be required to be
called upon historically for frequency of being drawn or no of weeks since.

I have tblLotto
DrawNo
DrawDate
Drawn1
Drawn2 etc to Drawn6
Sup1 & Sup2

I was think of using a Union Select, something like this.
(bare in mind, I know this doesn't work, coz I tried it, lol.)

Select [Drawn1]
From tblLotto
Where [Drawn1] = 1 (SELECT Count([Drawn1]) As No1 FROM tblLotto)
Union
Select [Drawn1]
From tblLotto
Where [Drawn1] = 2 (SELECT Count([Drawn1]) As No2 FROM tblLotto)
Etc to Drawn1 = 45

Of course this step will have to be repeated for Drawn2, 3, 4, 5, 6, Sup1 &
2. So in total there would be somewhere in the order of 360 Union Select
Statements required to complete this task. (What's That I hear you say,
"This Guy's A FrootLoop", your probably right, alas, nothing ventured,
nothing learnt) I know this is a big ask, and most likely the query will
take forever to calculate once the tables become densely populated with
data. But I do so love a challenge, I am certainly looking forward to any
suggestions you have to offer.

TIA

Reg's

Mark.
 
You can get the draw statistics for a given number by using two queries.

The first query, which in my example gets its parameter from a textbox on a
form, gives a recordset containing the dates on which the number was drawn:

SELECT tblDraws.DrawDate
FROM tblDraws
WHERE (((tblDraws.Drawn1)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Drawn2)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Drawn3)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Drawn4)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Drawn5)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Drawn6)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Sup1)=[forms]![frmLotto]![tboxNum])) OR
(((tblDraws.Sup2)=[forms]![frmLotto]![tboxNum]));

This is used in a second query, which gives the most recent date and the
number of times drawn:

SELECT Max(qryDrawStatsStep1.DrawDate) AS MaxOfDrawDate,
Count(qryDrawStatsStep1.DrawDate) AS CountOfDrawDate
FROM qryDrawStatsStep1;

Use as is, or modify to suit your particular needs. You could use this as
the basis of VBA code to loop through all numbers, instead of using the
number in textbox control, or ....

Hope this helps,

Rob
 
Back
Top