VBA Help: Lottery Number Matching (1 Viewer)

Punice

Registered User
Joined
May 10, 2010
Messages
116
Looking for some suggestions to enable me to write some code, for when a control button on a form is pressed, that will match, for example, 5 numbers that I pick with 5 winning numbers that the lottery draws AND save how many of the numbers match. (I know how Excel is an easy way to do this, but I want to use Access 2007.)
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,573
Huh? You've described a form with 1 button on it. How does it know what you've picked and how does it know the lottery drawn numbers? Please paint a better picture.
 

Punice

Registered User
Joined
May 10, 2010
Messages
116
Well, 'Huh', the form contains the fields where the 5 play & pick numbers are entered that are saved in a table. However, why does that matter when what I was asking for was guidance from you experts who know how to write code that will compare one set of five numbers with another set of five numbers and display the matching numbers count on a form. Not looking for a complete subroutine, just help.
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,573
I'm not looking for a complete workup with images and 5 pages of project guidelines, just help with helping you.

If you go to your mechanic and say "My car doesn't start. ", and he asks about your make and model, the last time it did start and other follow up questions, do you stomp your feet and say "Hey, I'm not asking you to rebuild the engine from spare parts, just tell me what I need to do to get my car to start".

So, Mr. Do-It-Yourself, what have you tried? Where are you stuck? And do you have any code at all I can look at to help with? I hope these questions aren't too prying.
 

MajP

The Dude or uh, His Dudeness, or uh, El Duderino
Joined
May 21, 2018
Messages
2,390
why does that matter
the Reason if matters is how you save or do not save the inputs. Without knowing that there could be many different ideas. Are these in a table, two tables? Are the fields the numbers or is each record a number? If each number is a record in a table and an you had another table with each record as a winning number. You could do a simple query which would match numbers and could show you the matches. If they are not records, but columns then the solution would be harder. Or maybe it is dynamic and nothing is in a table?
 

Punice

Registered User
Joined
May 10, 2010
Messages
116
I understand. You are right. I wasn't clear.

All numbers that I play are manually entered in individual fields on my form (frmPlay).

The control sources for these fields are: 'PlayNum1 through PlayNum5' & all numbers the lottery draws are: 'WinNum1 thru WinNum5'.

The entered numbers are saved in a table (tblPlay), when I pick the 'Save' control in the banner of my form.

I have another control for displaying the matched number count 'NumCntr' and a final one to execute the 'find match count' routine on-click.
 

plog

Banishment Pending
Joined
May 11, 2011
Messages
9,573
Since you are saving the winning numbers and the ones you picked, there's no need to "save" how many numbers match. You can do this via a query and then just reference the query when you need that data.

Your table of numbers should look like this:

tblNumbers
num_ID, autonumber primary key
ID_Drawing, number, foreign key to tblDrawing which holds data about the drawing these numbers are for
num_Value, number, the actual value of the number
num_Type, text, this determines if the number was a number chosen by you ("Played") or a winning number of the drawing ("Winner")

So that 1 table will hold both your guesses and the winners for a drawing. Then, to get your matches you create a query that has that tblNumbers in it 2 times. You link them via ID_Drawing and num_Value, bring down the num_Type fields from both tables, put "Played" in the criteria for one of them and "Winner" under the other. That will tell you what numbers matched between your chosens and the actual winners.
 

Punice

Registered User
Joined
May 10, 2010
Messages
116
Hello MajP,
I think I'd be better using two tables; one where I enter the numbers the lotter draws and one that contains the numbers that I play, which are always the same set of numbers.
However, I still need the matching number count routine code approach to use.
 

Punice

Registered User
Joined
May 10, 2010
Messages
116
Ok MajP, I'll work on writing the code and creating the query to do what you suggested.
I'll forget about using two tables that I mentioned in my previous response.

Thanks for tolerating my snarkyness. I can blame it on banging my head against the wall for about two weeks trying different approaches to finding a solution to my matching problem.

I could have begged for all of the code to solve my problem, but one doesn't become an Access wizard that way.
 

Punice

Registered User
Joined
May 10, 2010
Messages
116
After printing & reading you last response, I saw that I DO need to use two tables.
I was too hasty saying what I did in my previous response.
Now, I will get to work.
Many thanks.
 

MajP

The Dude or uh, His Dudeness, or uh, El Duderino
Joined
May 21, 2018
Messages
2,390
I would go with plog's normalized approach. In the long run much more flexible

Assume you had two non-normalized tables
tblMyPick5
Code:
ID	Type	Date	PlayNum1	PlayNum2	PlayNum3	PlayNum4	PlayNum5
1	Pick	12/21/2018	12	34	22	11	15
2	Pick	12/21/2018	11	76	54	32	7
and tblWinners

Code:
ID	Type	Date	WinNum1	WinNum2	WinNum3	WinNum4	WinNum5
3	Winner	12/21/2018	34	22	32	7	8
You could make a function
Code:
Public Function GetMatches(PickID As Long, WinnerID As Long) As String
  Const TablePick = "tblMyPick5"
  Const TableWin = "tblWinners"
  Dim rsPick As DAO.Recordset
  Dim rsWin As DAO.Recordset
  Dim i As Integer
  Dim j As Integer
  Dim strMatch As String
  Dim count As Integer
  Dim strsql As String
  
  strsql = "Select * from " & TablePick & " where ID = " & PickID
  Set rsPick = CurrentDb.OpenRecordset(strsql)
  strsql = "Select * from " & TableWin & " where ID = " & WinnerID
  Set rsWin = CurrentDb.OpenRecordset(strsql)
  For i = 1 To 5
    For j = 1 To 5
      If rsPick.Fields("PlayNum" & i) = rsWin.Fields("WinNum" & j) Then
        count = count + 1
        strMatch = strMatch & rsPick.Fields("PlayNum" & i) & "  "
      End If
    Next j
  Next i
  If strMatch = "" Then
    GetMatches = "No Matches"
  Else
    GetMatches = "Count Matches: " & count & "  " & vbCrLf & "Matches: " & strMatch
  End If
End Function
If you call that function and pass in 1,3 it returns the string

Code:
Count Matches: 2  
Matches: 34  22
If you pass in 2, 3 then
Code:
Count Matches: 2  
Matches: 32  7
If you saved winners and plays in the same non-normal table you could do something. If you do it like plog states you would really not need code.
 

Punice

Registered User
Joined
May 10, 2010
Messages
116
Holy Cow, I didn't expect that. I peeked at Plog's approach, earlier. I guess I should have paid more attention to it. Another case of my being in a hurry. Allow that, I'm 83.5 years old and still trying to be useful or win the lottery, which ever comes first.
Again, thanks for all that you did for me. Now, I can go in the house and take a nap. Tomorrow, I'll get to work.
 

MajP

The Dude or uh, His Dudeness, or uh, El Duderino
Joined
May 21, 2018
Messages
2,390
I peeked at Plog's approach, earlier. I guess I should have paid more attention to it.
Unless you have done a lot of database work that "correct" data structure may be difficult to understand and more difficult to build a simple form. But it drastically simplifies many other things and will always be flexible. The code I wrote is relatively complex to do a simple task. You have to create two recordsets and then loop all the fields in one and compare to all the fields in the other. Using plogs data structure you just do a simple join with almost no code. Databases are great for comparing different records but not for comparing data in different fields.

If you want, you can post a database and people can look at it to help.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom