[bambamn007]

Let's say I want to post results for a player on my team, for all the tourneys he has played in. Eash tourney has been saved as a table. Let's say I want my scores. Bam. And the tables are named, tourney1, tourney2, tourney3...ect I need a statement that will get just the column named total for table tourney1, tourney2, and tourney3 and display them in different cells on the output table.
SELECT total from (would i just separate the tables with a comma here?) WHERE name = 'Bam'

Second, I want a column/field at the end that will get the AVG of the different scores returned.
Third, I want a column/field after that that will have the AVG * .8 for a handicap.
Can this be done?
Bam

[andrewst]

Originally posted by bambamn007
Let's say I want to post results for a player on my team, for all the tourneys he has played in. Eash tourney has been saved as a table. Let's say I want my scores. Bam. And the tables are named, tourney1, tourney2, tourney3...ect I need a statement that will get just the column named total for table tourney1, tourney2, and tourney3 and display them in different cells on the output table.
SELECT total from (would i just separate the tables with a comma here?) WHERE name = 'Bam'

Second, I want a column/field at the end that will get the AVG of the different scores returned.
Third, I want a column/field after that that will have the AVG * .8 for a handicap.
Can this be done?
Bam

Well if you had a table for each tourney, you would have made a big mistake! There should be ONE tourney table, with a column like tourney_number to identify the separate tourneys.

You can get the average score for each player like this:

SELECT name, AVG(total) FROM tourney
GROUP BY name;

[bambamn007]

Well, the problem is this. Each tourney has 4 rounds. So I have to clear the board after each tourney. I have a column for each round, and a column for total.
So when I clear it in the actual DB, I rename it to another table name. SO now, my DB has the results table ( the one that the individual round scores get posted to) and the specific tourney tables.. Does that make sense?
Look here to see the output on the page. This contains info from one table, but 2 different criteria's.

http://www.gig-golf.com/GIG/ihtstandings2.asp

So what you are saying, is I can't get info from multiple tables in 1 DB?
Bam

[andrewst]

Originally posted by bambamn007
Well, the problem is this. Each tourney has 4 rounds. So I have to clear the board after each tourney. I have a column for each round, and a column for total.
So when I clear it in the actual DB, I rename it to another table name. SO now, my DB has the results table ( the one that the individual round scores get posted to) and the specific tourney tables.. Does that make sense?
Look here to see the output on the page. This contains info from one table, but 2 different criteria's.

http://www.gig-golf.com/GIG/ihtstandings2.asp

So what you are saying, is I can't get info from multiple tables in 1 DB?
Bam

Your design is totally the wrong approach for a relational database. You have a continually growing number of tables, and you want to query across the data in those tables. So every time you have a new tourney, you are going to have to amend all the queries that work across more than 1 tourney. It can be done, but no one does it that way!

There is no need to "clear" the table for each new tourney. You just need a tourney column in the table. Queries that are only concerned with the current tourney could say "WHERE tourney_no = <latest tourney no>". Queries that want to gather statistics across many tourneys would simply not specify a tourney_no value.

Where I work, each week I have to enter my timesheet details into a timesheet table. They do not "clear down" the table every week to make room for the new timesheet, they have a single timesheet table with a column called "week_commencing". The timesheet entry screen only shows 1 week at a time, but all the weeks are in the table and can be reported on.