If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Selecting values from a single table
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 |
#2
|
|||
|
|||
Selecting values from a single table
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; "Trodent" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|