7 Replies Latest reply on Aug 29, 2013 8:15 AM by chetgunhus

    Sports League stats

    tomo17

      Title

      Sports League stats

      Post

           I have a database that collects a range of match information for teams in a league. Currently i can call up season summaries for a selected team but what I am looking to do is go a step further and get season summaries/averages for when that team has been the opponent.

            

           My database is currently set up with the following tables:

           Teams Table - Team ID, Team Name

           Match - MatchID, Home team IDfk, Away TeamIDfk

           Match Stats - Match IDfk, Team IDfk, stats fields

            

           How do I call up the matches where Team A (ie either Home team or away team) has played (this i can do) but get all of the opposition stats?

            

           Any suggestions greatly appreciated

        • 1. Re: Sports League stats
          philmodjunk

               Are you trying to produce a found set of records in Match Stats that contain all the data for the opposing teams for the games placed by a specified team?

          • 2. Re: Sports League stats
            tomo17

                 Coming back to my list of things to do on this database and this is causing me the most difficulty.  My aim is to create a list view where i can compare the season averages for a team.  It will be used to create a standings ladder of sorts.  It would be such that I would see Team A - points for and points against, Team B points for and points against etc.  If and once this can be achieved I would then expand this to other statistical data that I have.

                 The current structure of the data is that each MatchStats record only shows information related to that team:

                 Match 1 , Team A, goals scored

                 Match 1 Team B, goals scored

                  

                 Do I need to restructure so that I have both teams data in one match record? (this may open up a whole new can of worms!)

            • 3. Re: Sports League stats
              philmodjunk

                   I think that your current relationships resemble this:

                   Matches 2-----<MatchStats>--------Teams-----<Matches>-----OpposingTeams (TO of Teams)

                   Is that correct?

                   The following relationship would link to the MatchStats of the opposing team:

                   Teams----<Matches>---OpposingTeams-------<OpponentMatchStats (TO of MatchStats)

                   Your reports/calculations would start from Teams and reach through Matches, OpposingTeams to the OppponentMatchStats table occurrence of MatchStatus to access the stats of the opposing teams.

              • 4. Re: Sports League stats
                tomo17

                     Ok -  will see if i can replicate what you have suggested.  

                     I currently have:

                     match - - - <Match stats>- - - Teams

                     which is in line with your assumption.  I am unsure how to now get the calculation fields working but I think i can see where you are pointing me. I will try and get back to you with the result.

                      

                • 5. Re: Sports League stats
                  tomo17

                       Have set up your tables as suggested.  My question is which realtionship do I create for the TEAMS table to the MATCH table as their is both a Home and and Away teamID.  Do i link it to one, and if so which one or do I link it to both?

                  • 6. Re: Sports League stats
                    philmodjunk

                         Teams Table - Team ID, Team Name

                         Match - MatchID, Home team IDfk, Away TeamIDfk

                         Match Stats - Match IDfk, Team IDfk, stats fields

                         Teams::Team ID = Match::Home team IDfk

                         and then

                         OpposingTeam::Team ID = Match::Away team IDfk

                         And OpposingTeam is a new table occurrence for the Teams table.

                    • 7. Re: Sports League stats
                      chetgunhus

                           Hi,

                           I am looking for a sports template for fencing and chess leagues that we run.  I've had a difficult time developing one on my one and am now looking to purchase one I can own and change the field names and calculations for our specific sports.

                           The template you are describing is interesting to me.  Do you have any interest in selling it?

                           Thanks,

                           Chet Gunhus