8 Replies Latest reply on Jun 10, 2011 6:57 AM by johnhorner

    Extracting Summaries from Table Tennis Match Database



      Extracting Summaries from Table Tennis Match Database


      i have a database that i keep just for fun to track the results of ping pong matches that are played among a small number of players (4-5) who get together regularly to compete.  at the moment the database has three tables: games, results, and players.  the games table has 2 fields: the date, and the game serial number.  it is related to results using the game serial number and data is entered into a portal that displays two rows (one for each player).  the results contains the game serial number, the player name, that player's score, and a field ("wins") which contains a "1" if that player won the game (auto-enter calculation).  it's ridiculously simple.  however, despite several hours of tinkering, i cannot figure out how to display some basic summary information.  for example, it would be nice to display on each "game" record what the players stats (win/loss pct, avg point spread, or total points won and lost for example) are with regard to each other over their history.  i know how to get a player's total lifetime wins against all opponents, but i don't know how to filter this for his "current" opponent (or by any opponent for that matter!).  it seems there should be an easy way to do this?  is this the wrong structure to begin with?  any thoughts would be greatly appreciated!

        • 1. Re: Extracting Summaries from Table Tennis Match Database

          Do you use one or two records to record a game result?

          Sounds like you use one.

          If you used two records--one for each player--with a field flagged with 1 to show the result record for the winner, I think you can then get the stats that you want here.

          • 2. Re: Extracting Summaries from Table Tennis Match Database

            i currently have it set up so that there are 2 "results" records (one for each player) for every game.  the record contains the players name, their score, and fields to indicate whether they won or lost (auto-enter calculations).  i tinkered a little more and by adding the opponents name in each game to the results record, i could then create a relationship to all results with the same combination of players... and from that calculate stats such as win/loss, but it seemed like cheating since the opponent's name is already recorded in it's own record.  it seems there must be a better and more elegant way to do this... no?

            • 3. Re: Extracting Summaries from Table Tennis Match Database

              This relationship can allow you to access data in the other player's result record for the same game:

              Results::GameID = SameGameResults::GameID AND
              Results::Player ≠ SameGameResults::Player

              SameGameResults is a new table occurrence of your results table. You can create this new table occurrence by selecting it in Manage | Database | Relationships and then clicking the button with two green plus signs.

              BTW, you can probably get away with using player names like this given the small number of participants, but it's better database design and avoids a number of issues for you if you define an auto-entered serial number in the Player table and use it in your relationships instead of a player's name. Player names are not unique, names change and you have problems fixing typos in the name after you've created related records.

              • 4. Re: Extracting Summaries from Table Tennis Match Database

                thanks phil... that was actually the first relationship that i tried, and while it does give you access to the opponent's results from the same game, i was unable to figure how to use it to calculate lifetime totals against that opponent.  that is when i came up with the workaround of including the opponents name as part of a players result record and then making a relationship where player = player and opponent = opponent.  i am still having a little trouble getting the opponents name to auto-enter correctly because when you make the first results record there is no opponent record to calculate from.  it works ok for the second record, but then you have to re-enter the name of the opponent to have the first record auto-enter the opponent's name correctly (otherwise it is left blank).  any thoughts?

                • 5. Re: Extracting Summaries from Table Tennis Match Database

                  Use a calculation field to return the name from the other result record. It will then update automatically when you edit the name in the other record. This calclation, of course, is unstored and that can be a problem in relationships, but in this case I think it will work for you as your stats will compute from the one results record to the many results records being used to compute your stats and unstored fields can be used on the "one" side of a relationship.

                  If you find you do need this field to be stored, you can use a script trigger to update a text field in the other result record each time you edit a name field.

                  • 6. Re: Extracting Summaries from Table Tennis Match Database

                    i just tried changing from auto-enter calculation to a calculation field and it works perfect updating the names, but it seems to have broken the relationship generating the stats (there is a "?" in all the stat fields).  i had tried using a "let" function in the calculation and use the opponents name in the other result record to be the "trigger", but for some reason it wasn't triggering the calculation.  does that only work with fields on the same layout or the same record?  i will try a script trigger as you suggested...

                    • 7. Re: Extracting Summaries from Table Tennis Match Database

                      Auto-entered calculations can only update automatically when the referenced fields come from the same record.

                      • 8. Re: Extracting Summaries from Table Tennis Match Database

                        ahhh, that explains it!  also, it took me a little while, but i finally got it workig with the script triggers... thanks!