Access newbie needs steer

I have a Golf tournament results database currently in Excel
Everything in one big flat file
Number of records has grown beyond Excel single sheet capacity (64k for my
version)
I have some access experience and can transfer my excel data to Access
I wonder if I should dump it all in one table, or multiple tables
I will be interested in querying an individual player's results from a
named set of tournaments over a specified time period
My field s are as follows:
Tournament ID
Tournament Finish Date
Player Name
Holes Played
final score
Finishing Rank
Prize Money
It strikes me that for each tournament there will be many (say, 170) records
with the same tournament ID in the tournament ID field
Is this good practice or should I be doing something different with multiple
tables?
Thanks

You should have the following tables:

tblTournament
TournamentID
TournamentName
TournamentFinishDate

tblPlayers
PlayerID
PlayerName

tblPlayerTournament
PlayerID
TournamentID
HolesPlayed
Score
Rank
Prize
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"David" wrote in message
...
I have a Golf tournament results database currently in Excel
Everything in one big flat file
Number of records has grown beyond Excel single sheet capacity (64k for my
version)
I have some access experience and can transfer my excel data to Access
I wonder if I should dump it all in one table, or multiple tables
I will be interested in querying an individual player's results from a
named set of tournaments over a specified time period
My field s are as follows:
Tournament ID
Tournament Finish Date
Player Name
Holes Played
final score
Finishing Rank
Prize Money
It strikes me that for each tournament there will be many (say, 170)
records
with the same tournament ID in the tournament ID field
Is this good practice or should I be doing something different with
multiple
tables?
Thanks

Thanks Arvin

I can see that this gives me a compact solution, especially if I want to add
more Tournament details, eg: tour (EGA, PGA, etc.), tournament comments, etc
also, assigning a unique player ID will keep me out of bother with duplicate
player names.
I suspect that having a tournament ID in the big tblPlayerTournament will
keep the database size down compared to using a long tournament name, eg:
TheMasters2009. Also, no need to concatenate T name with date to remain
unique...

Very helpful, thanks again

"Arvin Meyer [MVP]" wrote:

You should have the following tables:

tblTournament
TournamentID
TournamentName
TournamentFinishDate

tblPlayers
PlayerID
PlayerName

tblPlayerTournament
PlayerID
TournamentID
HolesPlayed
Score
Rank
Prize
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"David" wrote in message
...
I have a Golf tournament results database currently in Excel
Everything in one big flat file
Number of records has grown beyond Excel single sheet capacity (64k for my
version)
I have some access experience and can transfer my excel data to Access
I wonder if I should dump it all in one table, or multiple tables
I will be interested in querying an individual player's results from a
named set of tournaments over a specified time period
My field s are as follows:
Tournament ID
Tournament Finish Date
Player Name
Holes Played
final score
Finishing Rank
Prize Money
It strikes me that for each tournament there will be many (say, 170)
records
with the same tournament ID in the tournament ID field
Is this good practice or should I be doing something different with
multiple
tables?
Thanks