Is appen query best? Not sure where to start...

[allen_jr]

Hello... I don't know the best way to do this, but this is what I need to do:

I have a table of "players" with main columns - total games played and total games won. Every time the players complete a match, they submit their scores (Lets say weekly) Once the scores are updated, I will run a query to determine the win/loss ratio

What i am wondering: since there are new scores every week, could I just start a new table every week and somehow "merge" or "add" the 'games won' and 'games played' fields, as long as they match with the correct player (identified by unique "player ID numbers")

I know there is a simple way to do something like this, I'm just not sure what it is...I need the simplest way to enter this data... the other problem is that data is not reported from ALL players every week.... Can I just create a new table that contains 3 columns : PlayerID, Games Won, Games Played and then somehow merge that with a master table to get accumulated scores/totals? And in that table, if a Player ID is used twice, will the data just be "added up" when inserted into the Master Table?

As you can tell I'm a little confused... I hope I have made it clear what I am trying to do....

 

[jzwp22]

I'm a little unclear about what you are trying to do, but my first reaction is that you need at least 2 tables. Your first table would hold the player info.

tblPlayers
-pkPlayerID primary key, autonumber
-txtFName
-txtLName

The second table would store a reference to the player, the date played or the start of the period (assuming a fixed period length) and the # of games won.

tblPlayerDetails
-pkPlayerDetailsID primary key, autonumber
-fkPlayerID foreign key to tblPlayers
-dteStart (date of start of period of play)
-GamesWon

The problem comes in with the number of games played. Do your players play in different groups each week? For example, lets say players 1,5, 7 and 9 play 10 games together in a week. The stats go like this:


Player|GamesWon
1|5
5|2
7|0
9|3

If this is your scenario, you do not need to (and should not) store the number of games played, you can calculate it when you need it. If this is more of what you are doing then the table structure I provided earlier would not be appropriate. To help you futher, can you go into more detail of how games are played and how the players relate to one another etc.?