LottoMining's Blog

For California's Fantasy 5 it happens about once every 15 draws that the computer picks 3 numbers from the same previous draw from anywhere from 1-10 last draws, like it did four times in the first table, shown at the right column.

Date	Draw1	Draw2	Draw3	Draw4	Draw5	Dr1 in last 6	Dr2 in last 6	Dr3 in last 6	Dr4 in last 6	Dr5 in last 6	DblCt	Draws Back	MultDbl	Actual Numbers	TrplCt	3 from X draws   Back	3 Repeat #s
4/22/2011	7	10	13	26	31	1	1	1	1	1	1	4,6,10	1	7,13,   10,26, 10,13	1	9	7,26,31
4/21/2011	2	6	19	27	31		1	1			1	8		19,31
4/20/2011	9	10	37	38	39	1	1	1	1	1	1	6,8	1	37,39,   10,38	1	3	9,37,38
4/19/2011	5	6	28	36	39		1	1		1	1	5		28,39	1	8	6,28,36
4/18/2011	3	7	13	14	28		1	1		1	1	7		13,28
4/17/2011	6	9	34	37	38	1			1	1	1	10		34,37
4/16/2011	10	21	26	29	30	1		1		1
4/15/2011	1	8	16	19	32			1	1
4/14/2011	17	25	28	37	39	1		1	1	1	1	6		17,39	1	4	17,28,37
4/13/2011	7	19	20	26	31	1	1	1	1		1	4		19,26
4/12/2011	10	11	13	22	38	1		1	1
4/11/2011	6	13	16	28	36	1			1
4/10/2011	17	20	28	30	37	1		1	1	1	1	3,7	1	30,37,   28,37
4/9/2011	15	19	26	28	34			1	1	1	1	6,9,10	1	28,34,   19,34, 15,19
4/8/2011	7	10	17	24	39		1		1	1

 

Assuming you have a strong hunch that 3 numbers are going to repeat from the same previous draw of anywhere in the last 1-10 last draws, how many times and different prior draws covered would provide an interesting return?  On 4/19 I re-played all 5 numbers from 4/11 5 times, a jpeg should show below, for 5 3 of 5 winners and I picked up a 3 or 5 in 3 other ways for a total of 8 3 of 5 winners on the LP prediction board and in real life for $136.  I made a "data table" that forms a matrix of how many times played multiplied by how many different previous draws are covered multiplied by an assumed $15 payout for 3/5 winner less how much you played.

For example, where the 4 meets the 3 in the matrix (in bold): you won four times by repicking all 5 numbers from three different prior draws four times, and let's say you won so $60 payout less (3*4) = $48.  Has anyone else tried this method or any variation thereof?

 

		X		T	i	m	e	s	w	o	n
	$15	1	2	3	4	5	6	7	8	9	10
D	1	$14	$28	$42	$56	$70	$84	$98	$112	$126	$140
a	2	$13	$26	$39	$52	$65	$78	$91	$104	$117	$130
y	3	$12	$24	$36	$48	$60	$72	$84	$96	$108	$120
s	4	$11	$22	$33	$44	$55	$66	$77	$88	$99	$110
C	5	$10	$20	$30	$40	$50	$60	$70	$80	$90	$100
o	6	$9	$18	$27	$36	$45	$54	$63	$72	$81	$90
ve	7	$8	$16	$24	$32	$40	$48	$56	$64	$72	$80
red	8	$7	$14	$21	$28	$35	$42	$49	$56	$63	$70
	9	$6	$12	$18	$24	$30	$36	$42	$48	$54	$60
	10	$5	$10	$15	$20	$25	$30	$35	$40	$45	$50

 

You might also lose so I'll add a "data table" that shows how losses can add up if you try the strategy across an array x-y combos played.

 

		X		t	i	m	e	s	w	o	n
	$0	1	2	3	4	5	6	7	8	9	10
D	1	-$1	-$2	-$3	-$4	-$5	-$6	-$7	-$8	-$9	-$10
a	2	-$2	-$4	-$6	-$8	-$10	-$12	-$14	-$16	-$18	-$20
y	3	-$3	-$6	-$9	-$12	-$15	-$18	-$21	-$24	-$27	-$30
s	4	-$4	-$8	-$12	-$16	-$20	-$24	-$28	-$32	-$36	-$40
C	5	-$5	-$10	-$15	-$20	-$25	-$30	-$35	-$40	-$45	-$50
o	6	-$6	-$12	-$18	-$24	-$30	-$36	-$42	-$48	-$54	-$60
ve	7	-$7	-$14	-$21	-$28	-$35	-$42	-$49	-$56	-$63	-$70
red	8	-$8	-$16	-$24	-$32	-$40	-$48	-$56	-$64	-$72	-$80
	9	-$9	-$18	-$27	-$36	-$45	-$54	-$63	-$72	-$81	-$90
	10	-$10	-$20	-$30	-$40	-$50	-$60	-$70	-$80	-$90	-$100

 

 Here's the one pic of the 8 x 3 of 5 and another unusual souvenir: the machine kicked out two replays with the exact 5 numbers when two different tickets with the same numbers were put into the machine as 2 of 5 "replays".

 The winning numbers for jpeg below was 5,6, 28, 36, 39 and I re-played all from 4/11 which was 6,13,16,28,39.  Every so often CA F5 will have 4 numbers repeat from the same previous draw and did have all 5 numbers repeat from 10 days back in 2000.  Of course the trick is in figuring which prior draw.

 

 

 

 

 

A recent LP forum topic requested a script of some kind to generate randomized picks from the user's defined list of numbers.  I wrote (a number of years prior) an Excel VBA script that does this.  The script needs a tab labeled "Last" and then you put your list in the K column.  One interesting aspect to the macro is that let's say you think digit 39 is very likely to come up so you list it two, three times or more in the list and you'll see it show up more often in the outputted list of 5 randomized draws at range M1:Q5.  The script can be modified to output to generate more randomized draws and the intUniq variable can be changed to allow more or less numbers in your pool to randomize have the draws all be lower than a specified number, etc.  Once again, I used/borrowed the BubbleSort sub from John Walkenbach, who has written several great books on Excel and VBA for Excel; this sub sorts arrays, a handy function indeed.  I forget the reasons why goto statements are frowned upon, though I've stopped using them myself; forgive me if they offend your programming sensibilities.

 

Sub GenerateFiveRandDraws() 
Dim x As Single, intDrw As Integer, ColRef As String, NumDy As Integer, sCol As String
Dim LastC5ol As String, intUniq As Integer, strDblChk As String, ChkRef As String, ArNums(1 To 5) As Integer
Dim Val1 As Integer, Val2 As Integer, Val3 As Integer, Val4 As Integer, Val5 As Integer, a As Integer, ValX As Integer
Dim rndstart As Single, intrndstart As Integer

Sheets("Last").Select
NumDy = 1
Do
    intDrw = 1
    Do
    ColRef = Switch(intDrw = 1, "M", intDrw = 2, "N", intDrw = 3, "O", intDrw = 4, "P", intDrw = 5, "Q")
      'accept RndNum iif it's in the list in Col, then test for no dupes
      intUniq = 1
DoOVER:
      Do
           '''double rnd to start at different areas in UniqNums array
           rndstart = Format((Rnd() * 0.31) * 100, "00")
           intrndstart = CInt(rndstart)
           intUniq = intrndstart + 1
            x = Format((Rnd() * 0.39) * 100, "00")
            LastCol = "K" & CStr(intUniq)
            intUniq = intUniq + 1
            If intUniq > 27 Then   '''chng to 26 from 30
               intUniq = 1
            End If
            ''''embed a check here/perhaps send to sub w/more bias checks
            If x = 0 Then
              GoTo DoOVER
           End If
      Loop Until x = Range(LastCol).Value
      sCol = ColRef & CStr(NumDy)
      Range(sCol).Value = x
     
      If intD5rw > 1 Then
        ChkRef = Switch(intDrw = 2, "M", intDrw = 3, "N", intDrw = 4, "O", intDrw = 5, "P")
        strDblChk = ChkRef & CStr(NumDy)
        If Range(strDblChk).Value = Range(sCol).Value Then
           GoTo DoOVER
        ElseIf intDrw = 3 And Range(sCol).Value = Range("N" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 3 And Range(sCol).Value = Range("M" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 4 And Range(sCol).Value = Range("N" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 4 And Range(sCol).Value = Range("M" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 5 And Range(sCol).Value = Range("P" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 5 And Range(sCol).Value = Range("O" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 5 And Range(sCol).Value = Range("N" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        ElseIf intDrw = 5 And Range(sCol).Value = Range("M" & CStr(NumDy) & "").Value Then
           GoTo DoOVER
        End If
      End If
      intDrw = intDrw + 1
    Loop Until intDrw > 5

    Val1 = Range("M" & CStr(NumDy) & "").Value
    Val2 = Range("N" & CStr(NumDy) & "").Value
    Val3 = Range("O" & CStr(NumDy) & "").Value
    Val4 = Range("P" & CStr(NumDy) & "").Value
    Val5 = Range("Q" & CStr(NumDy) & "").Value

 For a = 1 To 5
    ValX = Switch(a = 1, Val1, a = 2, Val2, a = 3, Val3, a = 4, Val4, a = 5, Val5)
    ArNums(a) = ValX
  Next
  a = a - 1
 Call BubbleSort(ArNums())

 Range("Q" & CStr(NumDy) & "").Value = ArNums(a)
 Range("P" & CStr(NumDy) & "").Value = ArNums(a - 1)
 Range("O" & CStr(NumDy) & "").Value = ArNums(a - 2)
 Range("N" & CStr(NumDy) & "").Value = ArNums(a - 3)
 Range("M" & CStr(NumDy) & "").Value = ArNums(a - 4)
 

 If Range("M" & CStr(NumDy) & "").Value = Range("N" & CStr(NumDy) & "").Value Then
    intDrw = 1
    GoTo DoOVER
 ElseIf Range("N" & CStr(NumDy) & "").Value = Range("O" & CStr(NumDy) & "").Value Then
    intDrw = 2
    GoTo DoOVER
 ElseIf Range("O" & CStr(NumDy) & "").Value = Range("P" & CStr(NumDy) & "").Value Then
    intDrw = 3
    GoTo DoOVER
 ElseIf Range("P" & CStr(NumDy) & "").Value = Range("Q" & CStr(NumDy) & "").Value Then
    intDrw = 4
    GoTo DoOVER
 End If

 
  NumDy = NumDy + 1
Loop Until NumDy > 5
MsgBox "done"

End Sub

'borrowed this from John Walkenbach, a writer of great Excel books
Sub BubbleSort(list() As Integer)
Dim First As Integer, Last As Integer, i As Integer, j As Integer, temp
First = LBound(list)
Last = UBound(list)
For i = First To Last - 1
  For j = i + 1 To Last
    If list(i) > list(j) Then
       temp = list(j)
       list(j) = list(i)
       list(i) = temp
    End If
  Next j
Next i

End Sub

I recalled seeing somewhere here on LP that the Wisconsin's 5 draw lotto had some very unusual draws due to it being a computer driven random number generator (ie the same 5 numbers repeated on 3 different days). After I extrapolated the all combos of 3 code to all combos of 2 and 4 I figured I'd run it against California, Texas, Florida and Wisconsin 5 draws history tables and then look at the results. Yep, Wisconsin is off the charts, it has more instances of 4 repeats than the other three states--almost twice as many -with a many "3 repeat" from the same previous draw too.

Wisconsin's Badger 5

A while back an LP poster observed that that many computer generated pick 5 games have a tendency to repeat in pairs. I agreed with this finding and it had been on my list of things to program in a database to see if other interesting findings showed up. Or, how these pairs looked when looking for instances when 3 numbers repeat from the same prior day; or when 4 numbers repeat from the same prior day (a rare event though it has happened).  So I wrote a database script that looks at combos of 3 (below) since I already had something for looking at pairs.  Lady luck was good to me when I wrote this code last Sat 5/15; I used it to hit 3/5 four times on the LP prediction board and 5 times in real life for 17*5 or $85 (Yes I did play some of it back in the days that followed.)

Here's the basic logic (pseudo code):

SELECT Fields()

FROM [Combos of 2]

WHERE Fields() IN [Combos of 3]

AND/OR Fields() IN [Combos of 4]

 

And these new tables would be the basis for ad hoc querying in the database.

 

The script loops thru a pick 5 historical draws table and gets all 10 combos of 3 of each draw and adds it to a new table.

In this case, the history table is for California's Fantasy 5, which is known to have some anomalies, as chronicled in the LP forums.

 

Here's a list of the two tables and their data types:

Table: Combosof3 (new table resulting from processing)

note: idx, Num1, Num2, Num3 must all be selected as a composite primary key

Fields:

Date datetime

idx number

Num1 number

Num2 number

Num3 number

NumX text

 

Table: tblData (history table of draws)

Fields:

Date datetime

idx number

D1 number

D2 number

D3 number

D4 number

D5 number

 

Here's the code; add it to a new Access DB module (it is DAO; it will work in Access 2000/2003/2007)(Credit for

the "BubbleSort" sub-routine goes to John Walkenback aka Mr Spreadsheet and author of many Excel books, two of which I bought):

 

Option Compare Database
Option Explicit

Sub GenerateCombosof3()
Dim db As Database
Dim rs, rsCombo As Recordset
Dim strSQL, strSQLcombo, strDate As String
Dim Indx As Integer
Dim ArNums(1 To 3) As Integer
Dim a, ValX, AppendCount As Integer
Dim Num1, Num2, Num3 As Integer
Dim strNumX As String

strSQL = "SELECT Date, D1, D2, D3, D4, D5, Idx " & _
              "FROM tblData " & _ 
             "ORDER BY Date DESC;"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

With rs
  rs.MoveFirst
  Do
    Call DeleteQDefIfExistsX("UD1")
    Call CreateUnionQDefX(rs!Date, "UD1")
    strDate = "#" & CStr(rs!Date) & "#"
    Indx = rs!Idx
    strSQLcombo = "SELECT UD1.Drw AS Num1, T2.Drw AS Num2, T3.Drw AS Num3 " & _
                  "FROM UD1, UD1 AS T2, UD1 AS T3 " & _
                  "WHERE (((UD1.Drw)<>[T2].[Drw] And (UD1.Drw)<>[T3].[Drw]) " & _
                  "AND ((T2.Drw)<>[UD1].[Drw] And (T2.Drw)<>[T3].[Drw]) " & _
                  "AND ((T3.Drw)<>[UD1].[Drw] And (T3.Drw)<>[T2].[Drw])) " & _
                  "ORDER BY UD1.Drw, T2.Drw, T3.Drw;"
   
    Set rsCombo = db.OpenRecordset(strSQLcombo)
    With rsCombo
    rsCombo.MoveFirst
    Do
       Num1 = rsCombo!Num1
       Num2 = rsCombo!Num2
       Num3 = rsCombo!Num3
       strNumX = CStr(rsCombo!Num1) & "-" & CStr(rsCombo!Num2) & "-" & CStr(rsCombo!Num3)
     
       For a = 1 To 3
           ValX = Switch(a = 1, Num1, a = 2, Num2, a = 3, Num3)
           ArNums(a) = ValX
       Next
       a = a - 1
       Call BubbleSort(ArNums())
       Num1 = ArNums(a - 2)
       Num2 = ArNums(a - 1)
       Num3 = ArNums(a)
                     
       DoCmd.SetWarnings False
       DoCmd.RunSQL "INSERT INTO Combosof3 ( [Date], idx, Num1, Num2, Num3, NumX ) " & _
                     "SELECT " & strDate & ", " & Indx & ", " & Num1 & ", " & Num2 & ", " & Num3 & ", '" & strNumX & "';"
       DoCmd.SetWarnings True
   
        rsCombo.MoveNext
     Loop Until rsCombo.EOF
    End With
     rs.MoveNext
  Loop Until rs.EOF

End With

Set rs = Nothing
MsgBox "done"

End Sub

Function CreateUnionQDefX(dteIn As Date, qdefName As String)
Dim db As Database, rs As Recordset, qdef1 As QueryDef, qdef2 As QueryDef, sDate As String

sDate = "#" & CStr(dteIn) & "#"
Set db = CurrentDb
Set qdef1 = db.CreateQueryDef(qdefName, "SELECT tblData.Idx, tblData.Date, tblData.D1 AS Drw " & _
"FROM tblData " & _
"WHERE (((tblData.Date) = " & sDate & ")) " & _
"Union ALL " & _
"SELECT tblData.Idx, tblData.Date, tblData.D2 " & _
"FROM tblData " & _
"WHERE (((tblData.Date) = " & sDate & ")) " & _
"Union ALL " & _
"SELECT tblData.Idx, tblData.Date, tblData.D3 " & _
"FROM tblData " & _
" WHERE (((tblData.Date) = " & sDate & ")) " & _
"Union ALL " & _
" SELECT tblData.Idx, tblData.Date, tblData.D4 " & _
"FROM tblData " & _
"WHERE (((tblData.Date) = " & sDate & ")) " & _
"UNION ALL " & _
"SELECT tblData.Idx, tblData.Date, tblData.D5 " & _
"FROM tblData " & _
"WHERE (((tblData.Date)=" & sDate & "));")

End Function

Function DeleteQDefIfExistsX(strIn As String)
Dim db As Database, rs As Recordset, qdf As QueryDef, qCt As Integer, x As Integer

Set db = CurrentDb
For Each qdf In CurrentDb.QueryDefs
   'Debug.Print qdf.Name
   If qdf.Name = strIn Then
      CurrentDb.QueryDefs.Delete (strIn)
      Exit For
   End If
Next
End Function

Sub BubbleSort(list() As Integer)
Dim First As Integer, Last As Integer, i As Integer, j As Integer, temp
First = LBound(list)
Last = UBound(list)
For i = First To Last - 1
  For j = i + 1 To Last
    If list(i) > list(j) Then
       temp = list(j)
       list(j) = list(i)
       list(i) = temp
    End If
  Next j
Next i

End Sub

For California’s Fantasy 5 draw on 8/23/08, it looks like pairs of numbers that have shown up in the last few days are going to repeat again for the 8/23 or 8/24 draw, so it would be an interesting experiment to run a balanced wheel of 32 plays when picking a subset of 10 numbers.  It looks like 3 repeat from the same draw could also recur for 8/23 or 8/24. Best o’luck…

 

The numbers are 2,3,4,14,18,24, 28, 30, 33, 39

 

 

Index	Num1	Num2	Num3	Num4	Num5
1	2	3	4	14	24
2	2	3	4	18	33
3	2	3	4	28	39
4	2	3	14	18	39
5	2	3	14	30	33
6	2	3	18	28	33
7	2	3	24	30	33
8	2	4	14	18	28
9	2	4	14	28	30
10	2	4	18	24	33
11	2	4	24	28	30
12	2	4	28	33	39
13	2	14	18	28	39
14	2	14	24	30	39
15	2	18	24	28	39
16	2	24	30	33	39
17	3	4	14	18	33
18	3	4	14	28	39
19	3	4	18	28	30
20	3	4	24	28	39
21	3	14	18	24	28
22	3	14	18	30	39
23	3	14	28	30	33
24	3	18	24	30	39
25	3	24	30	33	39
26	4	14	18	28	33
27	4	14	24	30	33
28	4	18	24	28	33
29	4	18	30	33	39
30	14	18	24	30	39
31	14	24	30	33	39
32	24	28	30	33	39

The draw for CA’s Fantasy 5 on 2/9/08 offers excellent opportunities to run the BobP “group wheel” (it specifies a 4/5 if certain conditions are met).  About six years ago, I recall the definition of “turnover” (on Saliu’s website), the average number of draws it takes for the various numbers to repeat.  I’m looking for all 5 numbers picked on 2/9 (or 2/10 i.e. very soon) to have been last picked somewhere in the last 6 draws.  I’ll have to drop a few numbers since there’s 24 unique numbers in the last 6 draws and the BobP group wheel specifies a pool of 20, so I guess I’m introducing bias into the pool but that’s the best for now.  On weekends and evenings I’m working on a balanced wheel of 32 records, similar to the 64 result set.  It’s slow going, but an interesting experiment.

 

No, I won’t be playing all 64 suggested plays; it’s just a very interesting experiment #2, since #1 did hit a 4/5;

here: https://www.lotterypost.com/thread/168276/5 )

 

5,6,9,10,12,13,14,15,20,21,23,25,26,29,30,31,34,35,38,39

 

I might re-run it later on 2/9 mid-day before the actual draw with slightly different #s.

Yes, I’ll be putting a few on the prediction board later today…

Feel free to pick your index/records below just for kicks

 

 

Index	Num1	Num2	Num3	Num4	Num5
1	5	12	20	26	34
2	5	12	21	29	35
3	5	12	23	30	38
4	5	12	25	31	39
5	5	13	20	29	39
6	5	13	21	26	38
7	5	13	23	31	35
8	5	13	25	30	34
9	5	14	20	30	35
10	5	14	21	31	34
11	5	14	23	26	39
12	5	14	25	29	38
13	5	15	20	31	38
14	5	15	21	30	39
15	5	15	23	29	34
16	5	15	25	26	35
17	6	12	20	29	38
18	6	12	21	26	39
19	6	12	23	31	34
20	6	12	25	30	35
21	6	13	20	26	35
22	6	13	21	29	34
23	6	13	23	30	39
24	6	13	25	31	38
25	6	14	20	31	39
26	6	14	21	30	38
27	6	14	23	29	35
28	6	14	25	26	34
29	6	15	20	30	34
30	6	15	21	31	35
31	6	15	23	26	38
32	6	15	25	29	39
33	9	12	20	30	39
34	9	12	21	31	38
35	9	12	23	26	35
36	9	12	25	29	34
37	9	13	20	31	34
38	9	13	21	30	35
39	9	13	23	29	38
40	9	13	25	26	39
41	9	14	20	26	38
42	9	14	21	29	39
43	9	14	23	30	34
44	9	14	25	31	35
45	9	15	20	29	35
46	9	15	21	26	34
47	9	15	23	31	39
48	9	15	25	30	38
49	10	12	20	31	35
50	10	12	21	30	34
51	10	12	23	29	39
52	10	12	25	26	38
53	10	13	20	30	38
54	10	13	21	31	39
55	10	13	23	26	34
56	10	13	25	29	35
57	10	14	20	29	34
58	10	14	21	26	35
59	10	14	23	31	38
60	10	14	25	30	39
61	10	15	20	26	39
62	10	15	21	29	38
63	10	15	23	30	35
64	10	15	25	31	34

This is a v1.0 draft of a quick introduction to the tables, queries and VB code section of the Access database used as a lotto data-mining tool.  Although I would agree with the sentiment that there are no shortcuts in learning programming (like learning music or Math you see it or you don’t) there are a few automation shortcuts in the Access database, such as the use embedded SQL (Structured Query Language) in the VB editor to enable getting things done quickly. 
 
The modern database is built around the relational model, which groups pieces of data into similar entities as a means to reduce redundant data (an oversimplification, therefore the "1^st draft" label to this bloogie).  Your table(s) will generally model or be defined by what you're trying to track. In this case it will be a Pick 5 game.  At the most granular level, you are generally asking questions about certain metrics, when did this # last show up? Or how many times in the last 7 10, 20, … 100 draws?  Once you have built one test, you build another…and so on.  Perhaps a new unexpected insight leads to yet another (15 is picked 5x in the last 6 draws… what other things happen when that occurs?).  I like the following metaphor to describe the most used parts of the Database: the tables are the chassis of the car, the queries are the engine, the forms are the doors and/or windows and the reports are the chrome.  This write up will focus on tables and queries and hopefully work its way to moving the queries to code so folks can build, test and win.  I’ll call it a draft because if there are questions, I’ll fill in some of the blanks and because this draft is by no means exhaustive to the topics.
 
Table Design The rules of table design can be thought of as a blend of Art and Science, but they “are really just formalized common sense” as famously quoted by Database guru and author C.J. Date.  Although the most often books list normalization rules up to “third normal form” we’ll concern ourselves with the first two when modeling/tracking attributes of a pick 5 lotto game.  First Normal Form states that data in the tables’ fields must be atomic, that is broken down to the smallest unit (example: store someone’s name as last name and first name separately, so if you have to specify a criteria on a name it is easier to do when one field contains one value only).  Second Normal Form has to be with not allowing redundant fields in the relational table structure.  One will want to employ the primary key concept, which enforces uniqueness in a column that in turn protects against duplicate data and/or missing values.
 
When you create fields for your DB tables, do not include spaces in field names "FirstName" (NOT "First Name") because when you use the field in code you'll have to bracket it  "[First Name]" otherwise you'll trigger an error.  Similarly don't start your fields with a digit or those other non Alpha-numeric fields eg. "-" because your code will think it is the subtraction operator which is likely not your intention.
 
Basic Queries  Most of requested uses of SQL (Structured Query Language) are straightforward, though there are many esoteric parts that can get tricky.  The basic structure is:
 
SELECT fieldname1, fieldname2
FROM YourTableName
WHERE fieldname1 = 123
 
When joining tables in a query, there are different kinds of joins that can be done that mirror some of the different kinds of relationships among tables and other queries that typically exist in a database (one to one, one to many, many to many is not supported directly; it requires an intermediate table for a lookup work-around).  When creating a query without specifying a join the engine will create a cartesian product of the all the records in the tables referenced in the query; a useful function for generating all possible combos of 3, 4 or 5 from a pool of x numbers.
 
There are basic SELECT queries and Action queries (INSERT, APPEND, UPDATE, DELETE).  You're not required to capitalize the Keywords, it is just good practice for the sake of readability; you are doing yourself a favor down the line when you return to the code and you want to quickly figure out its meaning without draining your brain of horsepower at 3PM or 3AM.  There are also Aggregate queries, which allow you to easily get Sums, Averages, Min, Max, Variance, Standard Dev; your basic "Descriptive Statistics" functions built in.  You can call your own user defined function, provided that it is already saved in your Module window of your database.
 
When learning how to work with queries, I'd strongly recommend going to the pull down "View" menu and select "SQL" view so you can read what you are doing, it is pretty close to standard English.  Once familiar with SQL it can be placed in the VB Editor as embedded SQL, which can be a fast method for automating various routines and functions.  Below is an example that can be strung together as part of a larger program to suit your needs.
 
' a brief example of SQL embedded in the VB editor; an update query is shown
Docmd.RunSQL "UPDATE tblData SET D1 = 2 WHERE D1=1;"
 
The UNION query is also a very useful construct.  It allows you to append one recordset to another, provided that both recordsets have the same fields, in the same order and are of the same datatype. 
UNION ALL will include duplicates in the resulting recordset, where UNION will quietly not include duplicate records.  You could add a WHERE clause to filter this by Date range, for example.
'save this query as "UNION_ALLDraws_1col"
SELECT tblData.D1
FROM tblData
UNION ALL
SELECT tblData.D2
FROM tblData
UNION ALL
SELECT tblData.D3
FROM tblData
UNION ALL
SELECT tblData.D4
FROM tblData
UNION ALL
SELECT tblData.D5
FROM tblData;
 
' this returns the frequency count of all 39 numbers , when query above has been named as instructed.
SELECT UNION_ALLDraws_1col.D1, Count(UNION_ALLDraws_1col.D1) AS CountOfD1
FROM UNION_ALLDraws_1col
GROUP BY UNION_ALLDraws_1col.D1;
 
 

Variables in your code  For this introductory article, I think we’ll keep the variables inside the function (“at the procedure level”) in which they operate to be able to skim over global variables.  The time during which a variable retains its value is known as its lifetime.  The value of a variable may change over its lifetime, but it retains some value.  When a variable loses scope, it no longer has a value.A procedure-level variable declared with the Dim statement retains a value until the procedure is finished running. If the procedure calls other procedures, the variable retains its value while those procedures are running as well. If a procedure-level variable is declared with the Static keyword, the variable retains its value as long as code is running in any module. When all code has finished running, the variable loses its scope and its value. Its lifetime is the same as a module-level variable.  Also always have "Option Explicit" at the top of each code or form module. Otherwise you will think that a variable is in scope because the code compiles, whereas what is acually happening is that a new (local) variable has been created on the fly.

 
  
DAO / ADO ("Data Access Objects", the old object model three generations prior, pre .NET)
Although this is the old way of doing things, I believe this syntax is easier to memorize and therefore get up to speed with.  Make sure to have a reference in the References dialog box set to Microsoft DAO 3.6 or 3.51 library for the compiler to recognize the code.  Below is a very basic loop that can be built upon after having some practice with it.  The ADO (ActiveX Data Objects) I wrote about 5 years ago has some sloppiness or bad habits, but it works and at the time I wanted to quickly prototype things and move on to the next.  I did put in the time to learn ADO because I was going to jump into the ASP.NET/ADO.NET technology.  It is a Rapid Application Development (RAD) technology that builds Web pages that can communicate with a back-end Database (it's optimized for SQL Server 2005).  Access wasn't designed for web use or multi-user use; you'll need to upgrade to SQL Server 2005… I think MSFT also has free, scaled down version of SQL available to compete with the “free” stuff out there.  I like all the productivity tools with Visual Studio 2005 Professional so it makes the work rather fun and very efficient.
 
'you could assign D1 in the loop or you could call a user defined function
'this code assumes you have a tblData table or query in your DB or the code will
'trigger a run-time error; error handling may be covered in a later draft
 
Sub BasicRecordsetLoop() 
Dim db As Database, rs As Recordset
 
Set db = CurrentDb    'use the current DB
Set rs = db.OpenRecordset("SELECT D1, D2 FROM tblData;") 
With rs
    rs.movefirst
    do
        rs.edit  ''' you'll get a run time error if there is rs.edit without rs.update
        rs!D1 = rs!D1 * 1.1
        rs.update    ' change won't happen if rs.update is omitted
        rs.movenext
    loop until rs.eof    'eof = End of File
End With
 
Set rs = Nothing  ' free up memory
 
End Sub
 
'Tables tblCtAll6, tblCtAll10, tblCtAll20 must exist in the DB and the fields will be Date, 1, 2, 3… 39 for the loop in subroutine "LoopCounts" going up to 39.  Datatype for digits should be Number. This code will get the frequency count of numbers 1, 2, 3 … 39 in the last 6, 10, 20 draws in an efficient manner (i.e. look at the line of code that begins "rs(strFld)=".
 
Sub SubRoutineThatCallsAnotherSub()
'this one passes an argument for code re-useability a design goal as you build things...
 
  Call LoopCounts(6)
  Call LoopCounts(10)
  Call LoopCounts(20)
End Sub
 
Sub LoopCounts(intX As Integer)
Dim db As Database, rs As Recordset, strFld As String, i As Integer, strNum As String, strTable As String
 
strTable = "tblCtAll" & CStr(intX)
 
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT *, Date FROM " & strTable & " ORDER BY Date DESC;") '
 
With rs
rs.MoveFirst
Do
   i = 1
  Do
      strFld = CStr(i)
      rs.Edit
      rs(strFld) = fxCountXBack(i, intX, CDate(rs!Date)) '
      rs.Update
      i = i + 1
  Loop Until i > 39
  rs.MoveNext
Loop Until rs.EOF
End With
 
Set rs = Nothing
 
End Sub
 
'below is some sloppy ADO (written 5 or 6 years ago) that is called in the sub above
'Please forgive the messiness below
Function fxCountXBack(Num As Integer, ItNum As Integer, dtDate As Date) As Integer
'counts how many times each number has been used in last 100 draws
Dim cnn1 As ADODB.Connection, StrRS100 As String
Dim rs100 As ADODB.Recordset, SumALL As Integer
Dim D1 As Long, D2 As Long, D3 As Long, D4 As Long, D5 As Long
Set cnn1 = New ADODB.Connection
Set cnn1 = CurrentProject.Connection
Set rs100 = New ADODB.Recordset
StrRS100 = "SELECT tblData.Date, tblData.D1, tblData.D2, tblData.D3, tblData.D4, tblData.D5" & _
" FROM tblData WHERE (((tblData.Date) Between #" & dtDate & "#  And #" & dtDate & "# -" & ItNum & ")) " & _
"ORDER BY tblData.Date DESC"
rs100.Open StrRS100, cnn1
'Debug.Print StrRS100
rs100.MoveFirst
SumALL = 0
    Do
        If Num = rs100("D1") Then
          SumALL = SumALL + 1
        ElseIf Num = rs100("D2") Then
          SumALL = SumALL + 1
        ElseIf Num = rs100("D3") Then
          SumALL = SumALL + 1
        ElseIf Num = rs100("D4") Then
          SumALL = SumALL + 1
        ElseIf Num = rs100("D5") Then
          SumALL = SumALL + 1
        End If
      rs100.MoveNext
    Loop Until rs100.EOF
 
fxCountXBack = SumALL
End Function
 
 
 
 
 
 
 
   
 
 
 
 

Micro ISV (Micro-Independent Software Vendor) I finished reading a really awesome book not too long ago, very highly rated  (on Amazon / MicroISV: From Vision to Reality).  It is a book for those people who may have had the dream of leaving “Cubeland”, that vast corporate expanse, and starting their own business around their computer-related product or service.  I finished reading the book and now revisiting certain pages as part of a checklist to get the list of to-do’s and guide future work.  The author creates a roadmap for traveling the road to independence away from the world, and mindset, of working for someone else since he did it at some point in his career, somehow jumping out of journalism and starting as an independent contractor building Information systems for various clients; and later selling a time and project management systems to help individuals be more productive.  Since he was a programmer while working towards independence, the book has the perspective on what your technology has to do, how it looks and where it is seen.  There are a great number of interviews with people who have successfully been down that road and have grown a variety of business entities from the ground up.  The links in the book alone, along with context on why you’d need them make the book worth its weight in gold.  Another emphatic point is that it is harder than it looks, and he does mention the old adage of why work for someone else for 40 hours a week when you can work for yourself for 80 – 100 hours a week.  Among the many helpful tips this book has: the author pointed out the importance of getting your spouse’s buy-in before you take on this new endeavor, otherwise it can cause a lot of grief on the home-front. 

The author also happens to be the 4th rated blogger (in the top 25) on the topic of improving all facets of a blog, while maintaining a separate blog on all things MicroISV.  I think all sorts of tech people here will find it a fascinating and invaluable read.  I myself will try to work more closely to the principles he freely writes about, and it will temper my own programming bias since the book points out that you have to wear 47 hats to get that endeavor carried forward.

Like the rare movie that you can watch more than once, I have gotten different pieces of info and slightly different perspective when referring back through various pages to work on getting all the building blocks in place.  This chap has written a remarkable compilation in that book and the free resource as well.

(this is a continuation of the other Dev log that chronicled the first 14 hours of starting a tricky project in my spare time, after weeks away doing life’s other things and bits and pieces of tech work)
 
 

      Due to the complexity and processing overhead involved with generating all possible combinations of 5 numbers in a pool of 15 numbers, I put it on "hold" temporarily and switched to programming the infrastructure for all combinations of 3 numbers where the range of numbers is between 0 – 9; your standard three draw lotto game.  The version one starts with generating all 1000 possible combo's with a separate switch for choosing the number of odd numbers to filter the 1000, as well as a function to specify the SUM range (of all three numbers added).  It is again using ASP.NET and a SQL Server 2005 database; one the big technologies for building Web based applications that can communicate with a SQL Server 2005 database online.  I was able to change gears (from the 5 draw code) and get the smaller scaled project done and launched ( the v1.0 version) done in about 6 hours (it is up there and FREE).  I was able to get a lot done rather quickly since I was able to inter-mix database stored procedures and code in a few classes to extend what a database can normally do.  Along the way I added a couple of User Defined Functions in the SQL Server 2005 database; this will open the door to other metrics being efficiently added in the near future.  I'm going to be coding as much as I can leading up to the holidays and then see where I'm at.  I’m pretty sure I can expand the current project and have something working by end of Thanksgiving weekend.  I hope to have the Ilya Bluskov (sp?) combinatorics book incorporated, though later. 

 

For the 3-draw application, there’s a function for returning the Odd count, another for the sum of all 3 draws (75% of the time the sum is between 6 and 19 when adding all three digits for CA Daily 3), a checkbox list is there that filters the criteria for all three numbers, where selecting 0, 1, 2, 3, 4, 5 in the checkbox list will return all combinations of those numbers and nothing greater than 6 in the result set is returned.  In the near future I’ll add a Multiple Select List box to return a “Kitchen Sink model” where all criteria can be return in a huge record-set.  Tester's for this 3 draw application, as well as new suggested metrics to code are welcome.

 
Since I have the 3-draw example working, I am now ready to return to coding the 5 draw engine; the rough part is that I’ve got to iron out sticking points and try some new methods along the way.  Picture a jeep stuck in the mud, or maybe it’s more like pushing a big rock or two up a hill.  It’s a strange, frustrating kind of fun.  More to be written along the way, once I figure out the way.

There were 16 unique numbers in the last six draws for California’s Fantasy 5 on 10/29/07, that is an all time low in the table I’ve built that records its daily count.  It has the last 3300 or so observations; one day I’ll get around to back-filling the rest of the dataset though 3300 will do for now.  The related fields in the table tend to show that when a low unique count shows up, there’ll be “replacement” or turnover, where numbers that haven’t shown up in a while fall back into favor.  The way more interesting metric is that there’s been a huge run of pairs of numbers repeating in the last 13 draws and it could recur yet again on 10/29/07.  I’m going to be playing my pesos today by only playing the last  5 or maybe even 4 draws only and that pairs or three numbers from x (1 – 6) draws back will repeat yet again. 

 

Date	Draw1	Draw2	Draw3	Draw4	Draw5	Draws Back	Actual Pairs
10/28/07	7	12	16	35	39	1,2,3,9	35,39, 16,39, 7,35, 12,35
10/27/07	24	26	35	36	39	2	35,36
10/26/07	2	10	16	25	39	2,3,7	2,16, 2,25, 2,25
10/25/07	7	10	28	35	36	9	28,36
10/24/07	2	3	16	23	24	1,9	2,23, 2,23
10/23/07	2	23	25	27	34	4,5,8,9	2,25, 2,27, 2,23, 23,25
10/22/07	7	13	14	19	24	5	13,19
10/21/07	8	29	32	36	37	8	29,37
10/20/07	3	11	12	15	28	4,6	12,28, 11,12
10/19/07	2	12	25	33	35	5	12,25
10/18/07	2	13	17	27	29	1	2,13
10/17/07	2	4	13	19	37	8	4,37
10/16/07	12	13	26	28	36	5	12,26

 

In a post from a few months ago, I mentioned that all 5 numbers will have been picked from the last 4 draws, well that occurred on 10/14/07.  No I didn’t win on that day, it snuck by since I was playing a slightly different strategy.

 
On the programming side, I did get further along on building code for wheeling or generating all combinations of 5 from a pool of 15 numbers.  A simple version is working; I’ve got to expand on it for it to be useful.  I just got sidetracked with so many other things.  Plus I think I’ll adapt it for pick 3 games first as a way of getting the overall concept working on a faster timetable…. this is just a brief update on the previous blog.  I have more building and writing to do on that topic as well.

10/6/07 After reading the post at (LP>Lottery Systems>Wheel Generator 1.5) I decided to convert the code I had written in an Access database that generates all possible combinations of 5 draws from a pool of 15 numbers (or any N of X).  The migration, which started last Saturday, would be to newer technology and a more powerful database engine: SQL Server 2005.  VB.NET would fill in as the programming language when it was quicker to prototype objects and functions than a stored procedure (sp is basically a query on steroids) in the database.  I was rather distracted since I did the work while hanging out with my wife and soon to be 3 year old daughter in the living room on Saturday/Sunday. 
 
09/30/07 is Sunday day two of this undertaking, same development style: hang out with family and work at same time or occasionally. First thing in the morning I’m looking at it with a fresh pair of eyes and a new perspective as I continue building it.  Maybe I undid the last thing I did yesterday while trying to keep working when tired.
 
By 10/01/07 6:11 AM California time, I am 1 hour into my third day of development, I’ve almost got pre v1.0  recordset function working in .NET.  Everything is in one class (thus far) as properties and reusable functions and a few stored procedures in the DB.  I spun my wheels trying the new version of Array object and ArrayList in this latest version of VB.NET.  I added a set of “ifs” as a temp workaround for the sake of “quick prototyping”, but also because I could not proceed to any next step without the array and sorting functions.  At 2PM, I get another good hour here at home to do some coding, quick debugging, and I’ve got it working!!  Or, I have what I think are the tricky parts completed, the remaining pieces should be straightforward.  I was so ecstatic that I took a break from this multi-hour fun though occasionally frustrating project.
 
Failed driver causes laptop crash: after giving wholehearted thanks to above, I restart my laptop and get copies of my latest work over onto to my more powerful machine where I also have Visual Studio and SQL Server 2005 installed; the programming environments I’m using for this project.  I really like working with the  Developer and DB tools it has built in; I liken it to being at the controls of a helicopter or small aircraft.
 
“Carpe (Dev) Diem” is my mantra du jour, “sieze the Development time at all hours of day” or when possible; that has taken on new importance now that my daughter has lost her interest in taking afternoon naps.  I temporarily got in a groove of programming time and progress and my household tasks quickly piled up.  Wife complains.
 
10/6/07 I return to the code that generates all combos of N from a pool of X  Now I’m thinking I also have to code in a number of filters, such as kick out draws where all 5 numbers are odd or even, or exclude the “1,2,3,4,5” types of draws.  I have a clustering query that breaks the draws into 5 buckets (1–8, 9-16, 17–24, 25-32, 33-39) and gets an aggregate count of the different types of distributions –that’ll be a starting point for what to filter out.  Better yet, I’ll add as much user defined functions as I possibly could.  Anyone have a other ideas to filter against?  I’m right near that point in the building phase of all this. I am hoping to get huge amounts done today and tomorrow 10/7. 

California’s Fantasy 5 has had 2 draws of the last 3 draws where 3 numbers repeated from X draws back.  In this case, 9/27 had the 6,16,26 repeat from 9/25 and 9/29 had the 3, 16, 36 repeat from 9/22; 7 draws back.  I missed it on 9/27 (though I knew we were in “triples territory”) but I hit it on 9/29, picking the 3, 16, 36 three times with the actual tickets and on the LotteryPost prediction board.      The draw where 3 repeat from X (X range = 1 – 10) draws back has occurred many times this year in 2007; my count is 39 times.  That is over the average and we are not even done with 2007.  There was a different variable whose short term average was lower than its long term average and it gave me an early indicator of a flurry of 3 repeating from X draws back between April and May this year.  I hit the triple on 5/7 and managed to get the fourth number since I limited the pool of numbers to choose from.  After recurring on 9/27 and 9/29 I wonder if it’ll lay low for a couple weeks.  Let’s see if it kicks out any early clues again.

For California's Fantasy 5, there has been a lot of pairs of numbers recurring lately.
When that happens, oftentimes there are 3 numbers repeating from the same day back.
I have found that that is a good way to win and perhaps get a 4/5.  Does anyone else use that method?

A number of years ago I was doing a few internet searches and some site
had mentioned that watching and picking pairs of numbers that appear to be hot
is a good method of winning.  I worked with that advice and tested it and also had very good luck --
so this may as well be a public service announcement to those who aren't familiar with that approach.
There could be 3 numbers repeating on 9/8/07 from either 9/5/07 AND/OR 9/2/07.

This is not only my two cents worth, it'll be my $12 worth, since that is the filtering
criteria I'll be playing, and I posted on the predictions board for 9/8.  It will also be my
bet for tomorrow since the "triple alert" radar will stay on for Cal's Fantasy 5 on 9/9 if it does not hit
tonight 9/8.  Below is a spreadsheet listing that shows pairs of numbers repeating since April '07;
the time when a huge run of pairs and three numbers repeating began.

More tomorrow.