Selecting values from a single table

[Trodent]

I would like to create a database that would allow me to track Prize money
pools.
I would like to setup 2 tables.
A Member would pick their top 4 golfers from a golf tourney, the member with
the 4 golfers that has the Total Prize money being won the the top 4 golfers
would win.
What would be the best way to accomplish this?

table1 - Golfer, Prize Money
table2 - Member, Golfer, Golfer, Golfer, Golfer.
I would some how set up relationships 1 to many. 1 member has many golfers.

I would just like to enter in the prize money on Sunday Night and have a
report created with the Member who picked the top 4 players. Also each member
selection must be unique.
Thanks

 

[KARL DEWEY]

Use three tables --
table1 - Golfer, Prize Money
table2 - MemberID, MemName
table3 - MemberID, Golfer, Pick Date

Tables 1 & 3 and 2 & 3 are one-to-many.

SELECT [table2].[MemName], Sum([table1].[Prize Money]) AS [Pool Winner]
FROM (table2 INNER JOIN table3 ON [table2].[MemberID] = [table3].[MemberID])
INNER JOIN table1 ON [table3].[Golfer] = [table1].[Golfer]
GROUP BY Sum([table1].[Prize Money]) DESC;