I have a slight problem. I am a newbie to databases but pretty good with spreadsheets. I have been asked to convert a national bowling ranking spreadsheet to a database. What happens is, if a bowler makes it to any or all of the predetermined tournaments, they are awarded points for that tournament. From here, points are added up and the players are ranked. I am not sure what tables to set up. I'm assuming I need tables for tournament names, bowler names. I am not sure if I need one for pts awarded to each bowler for the individual tournaments. I want to be able to enter the bowler info (name, address, etc.), then on a separate form, select a bowler from the already setup list and be able to enter the pts for each tournament as required.
Later i can display the bowlers, total pts for the year and their ranking. I have tried myself to no avail. I have chosen Filemaker Pro as the database.
I have always said that are 2 type of people in this world, database people and spreadsheet people.
Unfortunately, the answer to your problem is not that simple. You were definitely on the right track when you mentioned a table for the Bowlers (Name, Address, etc.). You need a unique identifier called a primary key to identify each of those bowlers (I'd advise against counting their names as unique). But beyond that, things get very tricky; as you yourself have found out.
Tournaments are a great exercise in database design which is why I'd imagine no one here has attempted a reply. There just simply is not enough information in this post to be able to help much. Besides, that is a pretty lengthy request for someone to actually come up with in a forum response.
My first suggestion would be to read up on relational table design and normalization. There are some good short reads everywhere on this topic but you will need to study the examples.
After that, you would probably be better to abandon the spreadsheet and use the models provided in the tournaments (schedules, forms, worksheets, etc.) Those usually track closer to how the database works.
But alas, I'll try to make a stab at it from what you have provided.
This simple 3 table design allows the following SELECT...
SELECT Bowler.LastName, Bowler.FirstName, SUM(BowlerTournament.Score)
FROM Bowler, Tournament, BowlerTournament
WHERE Bowler.BowlerID = BowlerTournament.BowlerID
AND BowlerTournament.TournamentID = Tournament.TournamentID
GROUP BY Bowler.BowlerID, Bowler.LastName, Bowler.FirstName
I'm sure you would build out well beyond this and there are things I have not considered. That's the problem with not being close enough to the previously mentioned things (forms, worksheets, etc.)
The ID's I mentioned are fairly arbitrary. You should be able to specify those columns as autonumber or something like that. I do not know how you DBMS works so...
Actually, I think your problem is pretty simple, and ss5416 hit it on the head with the three table design.
One table holds bowlers.
One table holds tournaments.
One table is used to establish a many-to-many relationship between the two.
Your challenge will probably come in denormalizing your spreadsheet data, especially if it is located on different spreadsheets. Spreadsheets don't force users to use them correctly, so budget lots of time for dealing with messy data.
I would agree with the 3 table design. In similar situations like this, I create some temp tables, import the data from the excel sheets into these tables, clean the data and import it into the actual tables. Getting them into the database tables you can write some SQL to weed out the bad records.