Can any one help with this matching problem!!

[ChristineC]

I'm doing a project for college which is nearly finished except for one last bit. The project is called the lotto ALerter. It emails users who match correctly 4 numbers or more in one line.

6 numbers plus a bonus number are drawn in any particulaar draw.
These numbers are stored in a database which I have created and are updated automatically after each successive draw.

I need a way to match these numbers with users selected numbers. (Is there a way I can write a query for it I'm using MS Access). I have been struggling with this for ages. If any you out there can help me with this or even give me some guidelines I would greatly appreciate it.

the layout of my tables are below

Thanks a million
Christine

Layout of tables:

1st Table
UserDetails Table

Name
UserName 'primaryKey
EmailAddress
password

2nd Table
Numbers Table:

LotDate
UserName 'Primary key,
NumbersLn1 'holds first line entered by users
NumbersLn2 'holds 2nd line enterd by users
NumbersLn3
NumbersLn4
NumbersLn5
NumbersLn6

Each line hold all 6 numbers selected by a user.
I need to match each number in the results table with each number entered by users. Upon matching 4 numbers correctly an email is sent to that user

3nd Table
Results Table: 'Hold details of a lottery draw

LotDate
Amount 'Jackpot amount
Num1 'hold 1st number drawn
Num2
Num3
Num4
Num5
Num6
Bonus 'holds bonus number drawn

The results table always only holds one record, upon insertion of new records, the previous old one is deleted.

 

[Alexandre]

I would need to know if the order sequence of the numbers must be respected in order to make a suggestion.

 

[ChristineC]

The order of the numbers does not need to be respected. If a user receives 4 matches or more on a line the need to receive an email.

 

[Alexandre]

Sorry, but there s stil something confusing me. When you say "Each line holds all 6 numbers selected by a user" does that mean that NumbersLn1 doesn t hold a single number but a combination of 6, and that each NumbersLn is a different combination chosen by the user and potentially a winning combination?
If yes, what format did you choose to store the combinations of numbers?

 

[ChristineC]

Yes that is the way they are in the database. The numbers are entered on the webpage delimited by commas.

 

[Alexandre]

Sorry for late answer. Server was down lastly.

I am not saying that the proposal I am making is the most efficient but it certainly does the job. I do not think it is possible to answer your question just through a query (nor a combination of queries), at least not with the structure you chose to store the data. I believe there must be some code involved .

So here is one possibility among others (you could probably for example use your NumbersLnX strings as lists for In statements in queries...),involving a function called by a query. With one more loop, all could be done by the same function/procedure (even returning the winners UserNames), I just chose this way to minimize the amount of code:

Option Compare Database
Option Explicit

Public Function IsWinner(Combination As String) As Boolean
Dim WinningNumber, ProposedNumber, Matches As Integer
Dim ArrCombination() As String
Dim db As Database
Dim rst As Recordset

' Gets the winning numbers from ResultsTable
' Note that if you decided to use the LotDate field to create an history
' of players combinations and winning sequences, you could use a where statement
' in the query to match winning combinations and played combinations with the same
' LotDate (then you would have to pass LotDate to this function as a date parameter)
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT ResultsTable.Num1, ResultsTable.Num2" _
& ", ResultsTable.Num3, ResultsTable.Num4, ResultsTable.Num5, ResultsTable.Num6" _
& " FROM ResultsTable", dbOpenSnapshot)

' Make sure that the variables are properly reset (Paranoia reigns)
Matches = 0
IsWinner = False

ArrCombination() = Split(Combination, ",") 'Transorm the combination string into an array of numbers

For WinningNumber = 1 To 6 'Review the winning numbers

For ProposedNumber = 0 To UBound(ArrCombination) ' Review the numbers in the combination
If (" " & ArrCombination(ProposedNumber)) = Str$(rst.Fields("Num" & WinningNumber)) Then Matches = Matches + 1
Next ProposedNumber

Next WinningNumber
If Matches >= 4 Then IsWinner = True 'If more then 4 matches in the combination, we ve got a winner
' Clear the variables to free up memory
Set db = Nothing
Set rst = Nothing
Erase ArrCombination

End Function


And here is the query:

SELECT NumbersTable.UserName
FROM NumbersTable, ResultsTable
WHERE (((IsWinner([NumbersTable]![NumbersLn1]))=True)) OR (((IsWinner([NumbersTable]![NumbersLn2]))=True)) OR (((IsWinner([NumbersTable]![NumbersLn3]))=True)) OR (((IsWinner([NumbersTable]![NumbersLn4]))=True)) OR (((IsWinner([NumbersTable]![NumbersLn5]))=True)) OR (((IsWinner([NumbersTable]![NumbersLn6]))=True));

Hope this helps


[This message has been edited by Alexandre (edited 04-12-2001).]

[This message has been edited by Alexandre (edited 04-12-2001).]

 

[Alexandre]

Just two more things:

1 Since you did not specify, the above proposal assumes that not two numbers can be the same in a combination entered by the User (ex: no "1,15,89,1,6,43").

2 you may want to add some error-handling code to prevent the above situation, and the case where NumbersLnX would include unallowed characters (eventually also the case where NumbersLineX is null or a zero-lenght string if not prevented through you form/table validity rules)

 

[ChristineC]

A big Thanks to Alexandre, for helping me with my Matching problem.

Cheers!!

Christine