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.

This would be way more easier if you redesigned your tables so that you had

one rec per user/number picked in the Numbers table, and one rec/number

drawn in the Results table.  If you set things up like that, you could join

the two tables on their respective number fields and count the number of

records you get back per user.  So for instance, if you call the number

field in the UserDetails table NumPicked, and the number field in the

Results table NumDrawn, this:



SELECT UserName, Count(*) as NumRight

FROM   Numbers INNER JOIN

       Results

ON     Numbers.NumPicked = Results.NumDrawn

GROUP BY UserName

HAVING Count(*) > 3 ;



would give you a list of all the users who had 4 or more numbers in common

with the ones that were drawn.



As it stands, I don't think I'd try to get your answer with a query--I'd

have to go to a VBA function (which is almost always a warning sign that the

table design is not quite optimal).  But maybe you'll get better advice from

someone else...



HTH,



-Roy



-----Original Message-----

From: 9724613@s... [mailto:9724613@s...]

Sent: Tuesday, April 10, 2001 2:56 PM

To: Access

Subject: [access] can anyone help with this matching problem





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.