Back-to-back hits of lotto numbers 1

[GKIL67]

Hello all, Happy Holidays!

I have a table, DRAWtbl, having the fields IDcounter, date and five number fields (each number field can have values 1-49).
I would like a function i.e. b2bhits("IDcounter", "number") that would return from the last record and up to the "IDcounter" the number of consecutive occurrences (if any) of the "number" and the date of the last occurence.

For example, the following DRAWtbl (don't mind the dashes):
IDcounter - Numbers - Date
1 5-9-18-32-33 01/01/2010
2 7-28-35-5-41 01/02/2010
3 2-7-22-33-5 01/03/2010
4 44-28-35-5-41 01/04/2010
5 22-11-14-17-33 01/05/2010
6 17-19-5-23-44 01/06/2010
7 2-7-13-28-5 01/07/2010

i.e. b2bhits(2,5) would produce:
3, 01/04/2010
2, 01/07/2010

In the last six records, the requested 5 appeared 3 times (records 2,3,4) and after a skip (on record 5) it appeared again 2 times.

As there are thousands of records, I would appreciate any assistance for an efficient way /function.

 

[traingamer]

Seriously?

I'd normalize my tables as suggested in the other threads you've started.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[GKIL67]

It's a 1NF, maybe the "numbers" could have been in one column.
That's the data I've inherited and have to cope with it!

My other threads have received proper responses which lead to resolution, I can't see the point of normalization...

Thank you for replying.
I wish I could follow better and I believe there can be a better response to help me with my issue.

 

[TheAceMan1]

How are ya GKIL67 . . .

Curious this. Do consecutives have to occur within the same year, or is year crossover allowed?

Example:

[tt][blue]IDCTR Numbers Date
2 7-28-35-5-41 12/31/2010
3 2-7-22-33-5 01/01/[red]2011[/red]
4 44-28-35-5-41 01/02/[red]2011[/red][/blue][/tt]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[GKIL67]

Hello TheAceMan1,

No, there can't be any year or month crossover.
That's the reason I call it back-to-back.

Thank you for being interested,
as well for your suggestions!

 

[GKIL67]

Hello all,

It's still a pending issue...

Maybe somebody, with enough experience, could take a fresh look and suggest a way to handle this?

What really matters is how to get the desired outcome (number of back-to-back occurrences of a given number)and not the way to do it (it could be a function or a query...)

Thanks! and happy new 2010!

 

[MajP]

tblDraw

IDCounter dtmDate num1    num2    num3    num4    num5

1  1/1/2010       5       5       18     32      33
2  1/2/2010       7       28      35     5       41
3  1/3/2010       2       7       22     33      5
4  1/4/2010       44      28      35     5       41
5  1/5/2010       22      11      14     17      33
6  1/6/2010       17      19      5      23      44
7  1/7/2010       2       7       13     28      5

qryNormalDraw

SELECT 
  drawTbl.dtmDate, 
  drawTbl.num1 as Num
FROM drawTbl
UNION
SELECT
  drawTbl.dtmDate, 
  drawTbl.num2 as Num
FROM drawTbl
UNION
SELECT
  drawTbl.dtmDate, 
  drawTbl.num3 as Num
FROM drawTbl
UNION
SELECT
  drawTbl.dtmDate, 
  drawTbl.num4 as Num
FROM drawTbl
UNION SELECT
  drawTbl.dtmDate, 
  drawTbl.num5 as Num
FROM drawTbl
ORDER BY 1;

dtmDate              Num
1/1/2010              5
1/1/2010              18
1/1/2010              32
1/1/2010              33
1/2/2010              5
1/2/2010              7
1/2/2010              28
1/2/2010              35
1/2/2010              41
1/3/2010              2
1/3/2010              5
1/3/2010              7
1/3/2010              22
1/3/2010              33
1/4/2010              5
1/4/2010              28
1/4/2010              35
1/4/2010              41
1/4/2010              44
1/5/2010              11
1/5/2010              14
1/5/2010              17
1/5/2010              22
1/5/2010              33
1/6/2010              5
1/6/2010              17
1/6/2010              19
1/6/2010              23
1/6/2010              44
1/7/2010              2
1/7/2010              5
1/7/2010              7
1/7/2010              13
1/7/2010              28

Public Function getBtoB(intNum As Integer) As String
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim intCount As Integer
  Dim tempDate As Date
  strSql = "Select * from qryNormalDraw where Num = " & intNum & " ORDER BY dtmDate"

  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  If Not (rs.EOF And rs.BOF) Then
    tempDate = rs!dtmDate
    intCount = 1
    If Not rs.EOF Then rs.MoveNext
    Do While Not rs.EOF
      If tempDate + 1 = rs!dtmDate And (Month(tempDate) = Month(rs!dtmDate)) And (Year(tempDate) = Year(rs!dtmDate)) Then
        intCount = intCount + 1
        tempDate = rs!dtmDate
      Else
        If intCount > 1 Then
          'Debug.Print intCount & "," & Format(tempDate, "mm/dd/yyyy")
           getBtoB = getBtoB & intCount & "," & Format(tempDate, "mm/dd/yyyy") & vbCrLf
        End If
        intCount = 1
        tempDate = rs!dtmDate
      End If
      rs.MoveNext
      If rs.EOF And intCount > 1 Then
          'Debug.Print intCount & "," & Format(tempDate, "mm/dd/yyyy")
           getBtoB = getBtoB & intCount & "," & Format(tempDate, "mm/dd/yyyy") & vbCrLf
         End If
    Loop
  End If

End Function

test
Debug.Print getBtoB(5)
results
4,01/04/2010
2,01/07/2010

Test
Debug.Print getBtoB(7)
results
2,01/03/2010

 

[GKIL67]

It's great MajP!
I have no words to compliment this effort...

However it took me a few hours to realize why it's not working! First I thought because of the european dates the original table contains, but the problem lies in the data I originally provided which is serialized dates.

For instance, in the above tblDraw if we change the date of the 2nd line from 1/2/2010 to 1/5/2010 (don't mind the following dates, they should ALWAYS be greater) the function returns null, but a 2 is desired!

So, what I did: I followed your code, a star is a star, and used the IDCounter instead of the date to do the b2b and I managed to get the date info, too. So now I hurry up back to finish it, issue is closed

Thanks a million!

 

[MajP]

OK, hope you got it to work. Not sure I understand your problem. Not sure how the IDcounter would work, because the code reads through the sorted records and checks that the next day is one day greater, and in the same month and year. If there is a missing day or the dates are not sorted then, not sure how the IDCounter could work. If I change the second line of my table, I still get the correct values. By serialized dates do you mean the datatype is something else than a Date? The easy solution in this case would be to base "qryNormalDraw" on another query that had a calculated field like
Cdate(serialDate) as dtmDate

I do not think you can do this calculated field directly in the Union query.

BTW this can all be done in a query, but I am not very good at Sql so I could write this funciton in 10 minutes and would take me a real long time to get the queries correct.

 

[MajP]

And I think you could do the conversion in the Union query

SELECT
cdate(drawTbl.serialDate) as dtmDate,
drawTbl.num1 as Num
FROM drawTbl
UNION...

 

[MajP]

Although I am not smart enough to figure out a pure sql solution, but I can improve the speed of my function by using a query that only returns values that are part of a sequence:
qrySequence

SELECT DISTINCT 
  NextDay.Num, 
  NextDay.dtmDate
FROM 
  qryNormalDraw AS PreviousDay 
INNER JOIN 
  qryNormalDraw AS NextDay 
ON 
  PreviousDay.Num = NextDay.Num
WHERE 
  (((NextDay.dtmDate)=[PreviousDay].[dtmDate]+1 Or (NextDay.dtmDate)=[PreviousDay].[dtmDate]-1))
ORDER BY 
  NextDay.Num, 
  NextDay.dtmDate;

This returns this

Num	dtmDate
5	1/1/2010
5	1/2/2010
5	1/3/2010
5	1/4/2010
5	1/6/2010
5	1/7/2010
7	1/2/2010
7	1/3/2010
17	1/5/2010
17	1/6/2010

then you can change the code to
strSql = "Select * from qrySequence where Num = " & intNum

This query is nice because it shows you all sequences for error checking.

 

[GKIL67]

I realize I didn't lay out the issue correctly, thanks for your patience! The game plays two times a week, but after I took a closer look at the records I noticed that the days aren't always standard!

The "next day" is NOT always one day greater, it could be more by 3 or 4 days. Also the "next day" could be on the change of a month or a year.

I'm using the IDcounter to check if the requested number is found again at the right next date/record.

...
If tempIDcounter + 1 = rs!IDcounter Then 
        intCount = intCount + 1
        tempDate = rs!dtmDate
        tempIDcounter = rs!IDcounter
      Else
        If intCount > 1 Then
          'Debug.Print intCount & "," & Format(tempDate, "mm/dd/yyyy")
           getB2B = getB2B & intCount & "," & Format(tempDate, "ddd dd/mm/yyyy") & vbCrLf
        End If
        intCount = 1
        tempDate = rs!dtmDate
        tempIDcounter = rs!IDcounter
      End If
      rs.MoveNext
...

It runs slowly, over 10K lines/records, I'm not sure yet...

The strSql = "Select * from qrySequence where Num = " & intNum is not good because it returns all occurrences, but we need to keep only the back2back (adjacent).

So in a sorted by date table and a counter that increments after the table is sorted, can it be a better/faster way?

It's not easy, thanks for putting me on the right track!!

 

[MajP]

Little confused

"next day" could be on the change of a month or a year.

No, there can't be any year or month crossover

This seems to be contradictory, But if I understand correctly the ID counter always shows the next day. It could be several days later, in the next month, or next year. And I guess you can crossover a month or year.

Can you post a real table of data. I use 4Shared.com, but there are several other sites that are free. It would make this a whole lot easier. Your original example does not seem to mirror reality.

The strSql = "Select * from qrySequence where Num = " & intNum is not good because it returns all occurrences, but we need to keep only the back2back (adjacent).

Maybe you missed the point. I build the qrySequence and run that through my function. So the function only loops through records in a back to back sequence.

There is probably several ways to speed this up. The first is probably to write you Union query to a temp table and work off that. Union queries can be real expensive.

The other thing I would do since this is historical data is loop the function for 1 to 49 and write the data to a table. So I run that procedure today back to the beginning and save all sequences in a table. So what if it takes a lot of time I can start and then go get lunch, but the answers are persistent. And only do it once. A week later I want to get any more sequences, now I only pull a weeks data(plus any on going streaks).

It is not that hard. Just need clear requirements.

 

[GKIL67]

Yes! Let's please stick to: "... the ID counter always shows the next day. It could be several days later, in the next month, or next year. And I guess you can crossover a month or year."

You are right about the sql, I thought you had left your function aside.

Your point on speeding up the process is clear.
Although I don't understand how can I write the multiple lines your function produces into a table/field. Must be a memo field... I guess, but that's a different story

I managed to post a small copy of data for numbers 11 and 15. The [DRAWno] is the IDcounter.
15 is of outmost interest because of it's 3 times in a row appearance. Running getB2B(15) produces:
4,Sun 26/04/2009
2,Sun 12/07/2009
2,Sun 27/09/2009
2,Sun 29/11/2009
But we need to get:
2,Sun 26/04/2009
1,Sun 12/07/2009
1,Sun 27/09/2009
1,Sun 29/11/2009

And for 11, instead of
2,Thu 13/08/2009
2,Sun 22/11/2009
we should get:
1,Thu 13/08/2009
1,Sun 22/11/2009

Thus, the sum of the numbers (2+1+1+1=5 for 15) is the total of b2b(15) occurrences. I haven't been able to fix this!

Also I tried to store this total in the TOTb2b, but no luck!

Much obliged!

B2B.mdb

 

[MajP]

I am absolutely clueless of what you want.

DRAWno	dtmDate	Num
1172	2/22/2009	15
1176	3/8/2009	15
1187	4/15/2009	15
1188	4/18/2009	15
1189	4/23/2009	15
1190	4/26/2009	15
1192	5/3/2009	15
1194	5/10/2009	15
1196	5/17/2009	15
1211	7/9/2009	15
1212	7/12/2009	15
1222	8/20/2009	15
1232	9/24/2009	15
1233	9/27/2009	15
1237	10/11/2009	15
1245	11/8/2009	15
1247	11/15/2009	15
1250	11/26/2009	15
1251	11/29/2009	15

As far as I can see
there is a streak of 4 ending on 4/26 (1187,1188,1189,1190)
a streak of 2 on 7/12 (1211, 1222)
a streak of 2 on 9/27
and a streak of 2 on 11/29

So I have no idea what this means

But we need to get:
2,Sun 26/04/2009
1,Sun 12/07/2009
1,Sun 27/09/2009
1,Sun 29/11/2009

You are going to have to explain this. Is this now a different problem? Or are you redefining what you want. Because this sure has nothing to do with any earlier post. The only thing I can guess is that if you have a sequence of 4 it is 2 separate back to backs, and a sequence of 2 is one separate back to back. So what is a sequence of 6? 3.5 back to back? But if that is what you are getting at I would think if I had hits on (1187,1188,1189,1190) I would have a sequence of 4, but 3 back to back not 2. 1187-1188, 1188-1189,1189-1190. I can only guess that to you 1187-1188, 1189-1190 are back to back and you start over after a back to back.

Also if you can please provide a large set of data. And the best would be in the raw un normalized state. At least several hundred. This will make demoing a large solution much easier. I then can query any range you want to discuss.

Please provide a clear statement of the results you are looking for.

 

[MajP]

Although I don't understand how can I write the multiple lines your function produces into a table/field

build tblOutput
Num
SequenceCount
SequenceEndDate

change the function into a procedure

Public Sub writeBtoB(intNum As Integer)
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim intCount As Integer
  Dim tempDate As Date
  Dim tempIDCounter As Long
  strSql = "Select * from qrySequence where Num = " & intNum
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  If Not (rs.EOF And rs.BOF) Then
    tempDate = rs!dtmDate
    tempIDCounter = rs!IDCounter
    intCount = 1
    If Not rs.EOF Then rs.MoveNext
    Do While Not rs.EOF
      If tempIDCounter + 1 = rs!IDCounter Then
        intCount = intCount + 1
        tempDate = rs!dtmDate
        tempIDCounter = rs!IDCounter
      Else
        If intCount > 1 Then
          Debug.Print intNum & " " & intCount & "," & Format(tempDate, "mm/dd/yyyy")
          strSql = "INSERT INTO tblOutput (Num,SequenceCount,SequenceEndDate) Values (" & intNum & "," & intCount & ",#" & tempDate & "#)"
          CurrentDb.Execute strSql
          End If
        intCount = 1
        tempDate = rs!dtmDate
        tempIDCounter = rs!IDCounter
      End If
      rs.MoveNext
      If rs.EOF And intCount > 1 Then
          Debug.Print intNum & " " & intCount & "," & Format(tempDate, "mm/dd/yyyy")
          strSql = "INSERT INTO tblOutput (Num,SequenceCount,SequenceEndDate) Values (" & intNum & "," & intCount & ",#" & tempDate & "#)"
          CurrentDb.Execute strSql
         End If
    Loop
  End If
 
End Sub

run the sub looping through 1-49

Public Sub testBtoBsub()
  Dim intCounter As Integer
  CurrentDb.Execute "Delete * from tblOutput"
  For intCounter = 1 To 49
    writeBtoB (intCounter)
  Next intCounter
End Sub

using the original data set

ID     Num SequenceCount SequenceEndDate
20	5	4	1/4/2010
21	5	2	1/7/2010
22	7	2	1/3/2010
23	17	2	1/6/2010

 

[MajP]

Although I do not know how you are handling odd sequences (is a sequence of 3 equal 1 Back to back or 1.5 back to back or something else). This can all be handles using a query on the output table. I will demonstrate two calculations:
sequence count divided by 2 and sequence count integer divided by 2.

So instead assume this is the output table to include some odd sequences

ID    Num SeqCount SequenceEndDate
20    5   4         1/4/2010
21    5   2         1/7/2010
22    7   3         1/3/2010
23    17  7         1/6/2010

qryOutputWithBackToBackCalc

SELECT 
  tblOutput.Num, 
  tblOutput.SequenceCount, 
  tblOutput.SequenceEndDate, 
  [SequenceCount]/2 AS CountBtoB, 
  [SequenceCount]\2 AS IntCountBtoB
FROM tblOutput;

Num   SequCount  SeqEndDate CountBtoB	IntCountBtoB
5       4	 1/4/2010    2          2
5       2	 1/7/2010    1          1
7       3	 1/3/2010    1.5        1
17      7	 1/6/2010    3.5        3

 

[GKIL67]

Dear MajP I regret any misleading... and replying to your earlier post, indeed I had to redefine the requirement as my original approach was wrong. Thanks to you the conclusion is: The number of b2b Hits = The number of Sequences - 1

Attached, please find the respective raw data, the union query and the code you instructed.

The xls is what I wanted to get as a final result, from the beginning. I don't know yet if there is a better way to get it, but this thread certainly produced sufficient knowledge.

Why I involved dates and different print out? Because I didn't have a good grasp of the whole issue, not only the programming stuff! It took your programming skills and patience to resolve it... Thank you!

P.S. I would have replied earlier but I had to take a nap, there is about 8 hours time zone difference between my posting and tek-tips. Then I grabbed a paper and a pencil

 

[GKIL67]

Actually, the correct statement is:
Number of b2b Hits = (Number of Consecutive Sequences) - 1

Thus,
A streak of 4 ending on 4/26 (1187,1188,1189,1190)
produces a total of 3 b2b Hits and the streaks of the separated pairs of 7/12, 9/27 and 11/29 produce 1 b2b Hit, each. Therefore for a b2b(15) we get a total of 6 Hits.

... I just wanted to clear this up. I guess the issue of the attached xls is a whole new challenging story!

 

[MajP]

http://www.4shared.com/file/191449668/35b35c96/BackToBack_V2.html

The above database does it all.
The procedure
testBtoBsub
Will create tblOutput and open qryOutput
qryOutput has all needed fields
qryTotalsAndPercent provides all the data in the xl sheet.

Even with 10k records this should be relatively fast. If it is too slow there are some tweaks to add.
qryOutput

Num	SeqCount DrawNo	SequEndDate  CountBtoB
2	2	1225	8/30/2009	1
2	2	1248	11/19/2009	1
3	2	1215	7/26/2009	1
4	2	1243	11/1/2009	1
5	2	1219	8/9/2009	1
6	2	1174	3/1/2009	1
6	2	1211	7/9/2009	1
8	2	1183	4/1/2009	1
9	2	1237	10/11/2009	1
9	2	1244	11/5/2009	1
10	2	1187	4/15/2009	1
11	2	1220	8/13/2009	1
11	2	1249	11/22/2009	1
12	2	1178	3/15/2009	1
14	2	1226	9/3/2009	1
15	2	1190	4/26/2009	1
15	2	1212	7/12/2009	1
15	2	1233	9/27/2009	1
15	2	1251	11/29/2009	1
16	2	1198	5/24/2009	1
17	2	1228	9/10/2009	1
17	2	1253	12/6/2009	1
18	2	1235	10/4/2009	1
21	2	1207	6/25/2009	1
21	2	1241	10/25/2009	1
21	3	1256	12/17/2009	2
22	2	1204	6/14/2009	1
22	2	1213	7/19/2009	1
22	3	1241	10/25/2009	2
22	2	1260	12/31/2009	1
24	2	1196	5/17/2009	1
25	2	1222	8/20/2009	1
26	3	1204	6/14/2009	2
26	2	1221	8/16/2009	1
26	2	1242	10/29/2009	1
27	3	1179	3/19/2009	2
27	2	1209	7/2/2009	1
29	2	1180	3/22/2009	1
33	2	1216	7/30/2009	1
34	2	1189	4/23/2009	1
34	2	1200	5/31/2009	1
36	2	1231	9/20/2009	1
36	2	1248	11/19/2009	1
38	2	1175	3/5/2009	1
38	2	1259	12/27/2009	1
39	2	1229	9/13/2009	1
42	2	1203	6/11/2009	1
45	2	1200	5/31/2009	1

qryTotals&Percent

num	BtoBCount BtoBPercent
1	0	0.00%
2	2	3.85%
3	1	1.92%
4	1	1.92%
5	1	1.92%
6	2	3.85%
7	0	0.00%
8	1	1.92%
9	2	3.85%
10	1	1.92%
11	2	3.85%
12	1	1.92%
13	0	0.00%
14	1	1.92%
15	4	7.69%
16	1	1.92%
17	2	3.85%
18	1	1.92%
19	0	0.00%
20	0	0.00%
21	4	7.69%
22	5	9.62%
23	0	0.00%
24	1	1.92%
25	1	1.92%
26	4	7.69%
27	3	5.77%
28	0	0.00%
29	1	1.92%
30	0	0.00%
31	0	0.00%
32	0	0.00%
33	1	1.92%
34	2	3.85%
35	0	0.00%
36	2	3.85%
37	0	0.00%
38	2	3.85%
39	1	1.92%
40	0	0.00%
41	0	0.00%
42	1	1.92%
43	0	0.00%
44	0	0.00%
45	1	1.92%
46	0	0.00%
47	0	0.00%
48	0	0.00%
49	0	0.00%