comparing two tables

I take part in a lottery in which you have to select 6 out of 49
numbers. At least three of your numbers have to come up in order for
you to win a prize.

In my MS Access database I have two tables: "lotto" and "triples".

"lotto" contains the results of the Lottery. It has six fields: Ball1,
Ball2, Ball3, Ball4, Ball5, Ball6.

Ball1 Ball2 Ball3 Ball4 Ball5 Ball6
4 18 40 19 6 1
15 18 34 24 11 44
1 4 6 18 34 25
39 22 10 37 33 28
49 44 35 4 46 29
40 1 6 18 15 11
etc

"triples" contains every possible combination of a triple set of
numbers from 1 to 49.

Field1 Field2 Field3
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
.....
1 6 18
.....
46 47 49
46 48 49
47 48 49


What I would like to have is a query which will find out how many
times a certain triple set of numbers appears in the results. If I
could find which combination of three numbers comes up most often then
at least I might have a chance of getting some of my money back.

If such a query was run comparing the samples above, it would result
in

1 6 18

as being the most popular combination.

Any ideas on how to design such a query? Thanks.

Hi:

Try this query --

SELECT First(Triples.Field1) AS [Field1 Field], First(Triples.Field2) AS
[Field2 Field], First(Triples.Field3) AS [Field3 Field],
Count(Triples.Field1) AS NumberOfDups
FROM Table2
GROUP BY Triples.Field1, Triples.Field2, Triples.Field3
HAVING (((Count(Triples.Field1))1) AND ((Count(Triples.Field3))1));

Regards,

Naresh Nichani
Microsoft Access MVP




"Spiro" wrote in message
m...
I take part in a lottery in which you have to select 6 out of 49
numbers. At least three of your numbers have to come up in order for
you to win a prize.

In my MS Access database I have two tables: "lotto" and "triples".

"lotto" contains the results of the Lottery. It has six fields: Ball1,
Ball2, Ball3, Ball4, Ball5, Ball6.

Ball1 Ball2 Ball3 Ball4 Ball5 Ball6
4 18 40 19 6 1
15 18 34 24 11 44
1 4 6 18 34 25
39 22 10 37 33 28
49 44 35 4 46 29
40 1 6 18 15 11
etc

"triples" contains every possible combination of a triple set of
numbers from 1 to 49.

Field1 Field2 Field3
1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
....
1 6 18
....
46 47 49
46 48 49
47 48 49


What I would like to have is a query which will find out how many
times a certain triple set of numbers appears in the results. If I
could find which combination of three numbers comes up most often then
at least I might have a chance of getting some of my money back.

If such a query was run comparing the samples above, it would result
in

1 6 18

as being the most popular combination.

Any ideas on how to design such a query? Thanks.