Query assistance

[Steve Z]

I have created a DB to use in a Powerball pool. I have on table for our
picks and one table for winning numbers. I would like to create a query that
would compare our picks against the winning numbers each day we play the
lottery and find the winners. I am not sure how to go about this. Any
thoughts?

Thanks,

Steve

 

[June7 via AccessMonster.com]

Create a report with a RecordSource that is a join of the two tables linked
by the numbers. Use the report builder or wizard to help.

 

[Steve Z]

I am not sure what you mean, and I think it would be complicated by the fact
that each pick in the winning numbers would need to be compared to 5 columns
in the ourpicks table. Does that make sense?

Steve

 

[June7 via AccessMonster.com]

Okay, would help to know your data structure. Following is what I pictured:
tblPicks
PickID
EventID
BettorID
BettorPick

tblBettors
BettorID
BettorName

tblEvents
EventID
EventDate
EventName

Query: SELECT * FROM tblPicks WHERE BettorPick = " & tbxPBnumber

That would give you all the BettorID's with pick matching a winning number.
If you want their name then make a query that joins to the tblBettors. If you
want all events then build a join query with the tblEvents and tblPicks and
again if you want bettor names include tblBettors. Join links would be based
on the ID fields. Use the Access query builder to aid in building queries. If
your table structure is as I think then could build query to find winners for
a specific event (a query for all events would be more complex than I want to
tackle). Example:
SELECT BettorName FROM table WHERE “ & _
“fieldNumber1 = “ & tbxDailyNum & _
“ Or fieldNumber2 = “ & tbxDailyNum & _
“ Or fieldNumber3 = “ & tbxDailyNum & _
“ Or fieldNumber4 = “ & tbxDailyNum & _
“ Or fieldNumber5 = “ & tbxDailyNum & “;â€

The syntax shown in example queries would be as appears in VBA code.

I am not sure what you mean, and I think it would be complicated by the fact
that each pick in the winning numbers would need to be compared to 5 columns
in the ourpicks table. Does that make sense?

Steve

Create a report with a RecordSource that is a join of the two tables linked
by the numbers. Use the report builder or wizard to help.

[quoted text clipped - 8 lines]

 

[June7 via AccessMonster.com]

Forgot to show a field in tblEvents for the winning number: EventPBNumber

Okay, would help to know your data structure. Following is what I pictured:
tblPicks
PickID
EventID
BettorID
BettorPick

tblBettors
BettorID
BettorName

tblEvents
EventID
EventDate
EventName

Query: SELECT * FROM tblPicks WHERE BettorPick = " & tbxPBnumber

That would give you all the BettorID's with pick matching a winning number.
If you want their name then make a query that joins to the tblBettors. If you
want all events then build a join query with the tblEvents and tblPicks and
again if you want bettor names include tblBettors. Join links would be based
on the ID fields. Use the Access query builder to aid in building queries. If
your table structure is as I think then could build query to find winners for
a specific event (a query for all events would be more complex than I want to
tackle). Example:
SELECT BettorName FROM table WHERE “ & _
“fieldNumber1 = “ & tbxDailyNum & _
“ Or fieldNumber2 = “ & tbxDailyNum & _
“ Or fieldNumber3 = “ & tbxDailyNum & _
“ Or fieldNumber4 = “ & tbxDailyNum & _
“ Or fieldNumber5 = “ & tbxDailyNum & “;â€

The syntax shown in example queries would be as appears in VBA code.

I am not sure what you mean, and I think it would be complicated by the fact
that each pick in the winning numbers would need to be compared to 5 columns

[quoted text clipped - 7 lines]