Hello Everyone:

I am trying to create a lottery checking program in Access 2007 (just
so I can practice my rusty skills). What would be the best approach
to comparing two sets of numbers? The user will enter their ticket
numbers (6 per record) in one table and then enter the Winning
numbers (7 per record) in another table. There is a relationship
between the the two tables based on the date of the draw.

What would be the best approach to loop through the ticket numbers and
flag those numbers that match the winning numbers?

Appreciate any ideas. Thanks in advance

mjbaz wrote:

numbers that hit will be red foreground, the others black.

I would think you'd need to make each number a separate column.

Most likely with conditional formatting you could highlight the numbers
that hit setting up multiple criteria.

On Mar 31, 10:18*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Ah, thanks. But not really answering my question. Anyone else?

mjbaz wrote:
Depends on how the numbers are entered. It would probably be easiest if the
table NumbersPlayed had columns for the date, user (assuming multiple users
are involved) and single digit of the number played, so that for each date,
there would be 6 rows for each ticket played. Similarly, the WinningNumbers
table would contain columns for date and single digit resulting in 7 rows
for each date. then join the tables on date and digit, group by user and
date, and use HAVING to select the users where count(*) = 6 (This assumes
that exact matches are required)... easy-peasy.

SELECT user,p.lotterydate FROM
WinningNumbers as w join NumbersPlayed as p on p.lotterydate= w.lotterydate
and w.ticketdigit = p.ticketdigit
GROUP BY user,p.lotterydate
HAVING count(*) = 6


Of course, if you insist on storing the entire numbers as text in single
fields, then I would be inclined to forse the structure into the above
structure using some saved union queries:
qryWinningNumbers:
SELECT lotterydate,mid(TicketNumber,1,1) as ticketdigit FROM WinningTickets
UNION ALL
SELECT lotterydate,mid(TicketNumber,2,1) as ticketdigit FROM WinningTickets
etc.

qryNumbersPlayed:
Same idea

Then, join the saved queries as above.

That is the "database" approach. If you are determined to practice your
looping skills, then I would imagine that a recordset to retrieve the
winning and played numbers for each date would suffice, and two loops (no
arrays involved) one nested in the other to do the comparisons. Something
like:

dem rs as recordset
dim numplayed as string
dim numwinning as string,wdigit as string
dim i as integer, j as integer
dim matchesfound as integer
'open the recordset and start looping through it:
do until rs.eof
numplayed=rs("NumberPlayed")
numwinning =rs("WinningNumber")
matchesfound=0
for i = 1 to 7
wdigit=mid(numwinning,i,1)
for j = 1 to 6
if wdigit = mid(numplayed,j,1) then
matchesfound=matchesfound + 1
exit for
end if
next
if matchesfound = 6 then exit for
next
if matchesfound=6 then 'flag the winning ticket/user
rs.movenext
loop

Frankly, I prefer the "database" approach ... :-)

On Mar 31, 10:41*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Thanks much. Now I know that my table structure is lacking. I have
two tables but I've set them up so that the numbers entered are
allocated to one of eight fields (Date, Num1, Num2, Num3, etc). So
there is only ONE record (parent) for the winning numbers and several
(children) records (each with 8 fields) - depending on number of
tickets purchased. My problem lied in trying to move horizontally
through the records (field by field) to make the comparison between
the winning numbers and the ticket numbers.

I guess I concentrated on the "prettiness" of the form and ignored the
practicality. I can make the form real pretty if use a one to many
relationship and a parent form and subform. But then it falls apart.

Thanks greatly for your input. Appreciate it.

mjbaz wrote:
Well, you can keep your current "pretty" design and use the union query
approach described above to "fold" your tables so the grouping query
approach can work. Instead of Mid( ... ) just use the appropriate field to
create each row.

On Mar 31, 12:16*pm, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Thanks everyone. Just needed to be pointed in the right direction.