Groups | Search | Server Info | Login | Register

Groups > comp.databases.ms-access > #720

Re: Need Advice - RecordSet or Array or Neither? Best Approach

Show all headers | View raw

mjbaz wrote:
> On Mar 31, 10:41 am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
>> mjbaz wrote:
>>> 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
>>
>> 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.
>>

>>
>> Frankly, I prefer the "database" approach ... :-)
>
> 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.

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.

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Need Advice - RecordSet or Array or Neither? Best Approach mjbaz <mjb5555ca@yahoo.ca> - 2011-03-31 06:34 -0700
  Re: Need Advice - RecordSet or Array or Neither? Best Approach Salad <salad@oilandvinegar.com> - 2011-03-31 09:18 -0500
    Re: Need Advice - RecordSet or Array or Neither? Best Approach mjbaz <mjb5555ca@yahoo.ca> - 2011-03-31 07:28 -0700
  Re: Need Advice - RecordSet or Array or Neither? Best Approach "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-03-31 09:41 -0500
    Re: Need Advice - RecordSet or Array or Neither? Best Approach mjbaz <mjb5555ca@yahoo.ca> - 2011-03-31 08:57 -0700
      Re: Need Advice - RecordSet or Array or Neither? Best Approach "Bob Barrows" <reb01501@NOyahooSPAM.com> - 2011-03-31 11:16 -0500
        Re: Need Advice - RecordSet or Array or Neither? Best Approach mjbaz <mjb5555ca@yahoo.ca> - 2011-03-31 09:49 -0700

csiph-web