Count number of wins, draws, etc for a team

[cybercider]

I have t_results tables that is as follows:

t_results
match_id
date
hometeam (t_teams.teamname lookup)
awayteam (t_teams.teamname lookup)
winner (t_teams.teamname lookup)

I am trying to produce a summary of stats for each team e.g. wins, draws, losses, home wins, home draw, home losses for starters. But I am getting nowhere with queries.

Can anyone point me in the right direction?

CC

 

[Galaxiom]

I notice you are not recording scores. Probably would be good information to include.

Your queries compare the values of the field winner and home team or away team as required and then group by team.

For example:

Home wins where clause in the sql:
WHERE t_results.winner = t_results.Hometeam GROUP BY t_results.Hometeam

Then add a count field.

However you should consider changing the structure at least slightly. Make the Winner field a HomeWin Yes/No field. This makes it easier to ask the questions you want. It also reduces the data storage and processing time.

Indeed, the original form allows the winner to be recorded as a team that didn't even play. This is in breach of normalisation.

Potentially you could also consider recording the match information separately from the outcome. There are two results for each match. Because these entities have a common structure they could go into another table and refer to the common information in the match table.

Match table would just contain MatchId, Date and LocationID.

Results would be in a table with fields:
MatchID, TeamID, Score

This structure allows you to easily query any team's score without having to search two different fields as would be the case in the original structure.

If you don't use actual scores then it can be a checkbox for win. Otherwise compare the two scores to determin the winner. (Note: You would never record both scores and a winner field because this would breach normalisation since the winner coluld be flagged contray to the score.)

To determine home and away information, compare the team name with the Location field of the match with the tables linked on MatchID.

This structure is not always immediately obvious to to new developers but it is an excellent example. Although it disperses the information related to the match more widely this is not a problem for a database. What really matters in database structure is avoiding having the same kind of information in different fields.

A structure focussed on centralising the data in the match table would be putting scores in two fields. This action is often a clue to the need to reconsider the structure.

 

[cybercider]

Thank you for the reply, a great help and if I've understood you correctly my new structure as attached is better?

Cc

 

[Galaxiom]

Wow you are really into the statistics.

tResults appears to be a subset of tResults_Detailed so would be redundant. Add the FixtureID to the detailed table and get rid of the simple results table.

Reduce the fields in tResults_Detailed to one field for each type. For example, ShotsHome and ShotsAway do not both apply to the same team. Just have Shots, Attempts, HitWood etc. Remember the new structure uses one record for each team per fixture. (Two records per fixture). Move right away from the concept of separating Home and Away at the table level. It is a derived value, calculated as required in the new structure.

Notice how you are recording the same type information for both teams in separate fields. Remember the golden rule is to store the same kind of data in the same field whereever possible. The other fields are used to identify the relationship to the team and match but the Shots information for either team is essentially the same kind of data regardless of the team.

Once again you are breaching normalisation. It is possible for your data to record less attempts than goals. Really you should not be recording Attempts, only their Outcomes. You can get the total attempts by adding the Goal, Misses, HitWood etc. There can then be no conflicting totals.

If you were a purist you could consider Locations in another table with information about the location. Its PK would be referenced to in both the Fixture table (as Venue) and the Team table (as the HomePitch). The HomeAway information would be recovered through a join via the the locations field and the HomePitchID. I only say this because the team and location are not technically the same. But I am being pedantic and it will certainly work as you have it.


Now I am moving into the specification rather than the design but I would go much much further. Ignore the rest of the post if you wish.

You might also consider adding fields the environmental conditions prevalent at the Fixture such as temperature and rainfall. These certainly affect the performance of a team and may give you insights into the most suited team under adversrse conditions for example.

Have a Player table with information about the player.

Have a Sides table to indicate who played in the match. It will include PlayerID and FixtureID foreign key fields. You could include the position they played, the time they spent on the pitch and number of shots they took etc. Basically most of your results information would be recorded against player and fixture rather than just fixture.

Going even further I would have a Shots table with field for PlayerID, MatchID, ShotTypeID, ShotTime, OutcomeID. The corners would just be a type of shot. Another table, ShotOutcome would hold the keys for Score, Miss, HitWood, Intercepted etc.

Pressing a button on the form against the player would enter the record with a time stamp and prompt to choose the outcome or have a set of buttons to cover each outcome.

This structure would make it easy to retreive any player or team shot stats from a match or the whole season.

You might be surprised that all this would go into the same table. Every shot, by every player player, every week for the whole season. Sounds like a lot of records? Well if there were 5000 passes in each match and 200 matches in a season Access could record and work with them all quite happily. (Working with the data entry operator might be another question. ).

Don't let the number of records freak you . It is better for Access to have more records with less fields.

I would do the same with a Penatlies table. PlayerID, PenaltyTypeID (Offside, Foul, etc). PenaltyActionID (GoalShot, FreeKick etc) Once again use IDs with a table to relate the name. You could include the Position on the field if your poor data entry operator didn't spit the dummy.

With player interchange I would have an on and off time records with the positon they takeup. This would supplant the TimePlayed field mentioned above in the Sides table. This would have MatchID, PlayerID, ChangeTime, On/Off etc. On/Off is not strictly necessary as you can calculate this. If a play is on then they must be coming off. But it is probably easier to query the data by including it.

This should give you something to think about. Think hard about what you really want to get out of the database. Design for expandibility. It is much easier to include features that will inevitably be suggested later than to try and add them to a structure that is not designed to accomodate extension.

 

[cybercider]

Your explanation on normalisation of data and how it is better to keep two records (one for each team) for each fixture makes sense now.

Hopefully, the attached shows a better understanding on my part of this!

Next step is to insert the historic data I have into the new tables, currently this is located all in a single CSV file some 3500 records.

My thinking was...

Break this down to resemble the table structure, add new columns for my PK's and assign a unique number. Then do a find and replace, replacing text with the unique number, e.g. if Arsenal's unique number (PK) is 1, I do a find and replace on the FixtureDetails data to replace Arsenal with 1. Before importing the data to the corresponding table.

I don't know what but I feel like I am missing something here regarding the joins between the information that is cross-referenced (TeamID in tFixtureDetails). Perhaps it will come clear or disappear when I try it now!

Thanks again for help, it's been of great help.

Cc

 

[Galaxiom]

Your explanation on normalisation of data and how it is better to keep two records (one for each team) for each fixture makes sense now.
Hopefully, the attached shows a better understanding on my part of this!

Now you are on the track. I like the way you added the Stadium and Official tables. Definitely will be interesting to recover some stats on the Referee. Particularly the awarding of penalties.

Next step is to insert the historic data I have into the new tables, currently this is located all in a single CSV file some 3500 records.

My thinking was...

Break this down to resemble the table structure, add new columns for my PK's and assign a unique number. Then do a find and replace, replacing text with the unique number, e.g. if Arsenal's unique number (PK) is 1, I do a find and replace on the FixtureDetails data to replace Arsenal with 1.

Probably best to import the data into a table in the orginal format. Import rather than link so it is read only. Access is the most powerful manipulation tool at your disposal. Use an update query to replace the Team Name with the ID. Join the imported table to the Fixture Data on Team name.

However take care to not lose records. Spelling mistakes, including subtlties like a trailing space, in the original data can leave records out entirely unless you use an outer join showing all original records. The spelling mistakes won't convert to an ID but will still appear. An inner join will leave out the record completely.

I don't know what but I feel like I am missing something here regarding the joins between the information that is cross-referenced (TeamID in tFixtureDetails). Perhaps it will come clear or disappear when I try it now!

With ths structure, using the team and fixture ID you can return any stat about any side at any match by querying the appropriate field in one table. Before it would have been harder to predetermine which field held the data for a particular team because you would first have to determine if they were Home or Away at the fixture. This would have most likely been acomplished with an IIF function which is way slower than an SQL join (and not compatible with more advanced data structures).

To query this data for fixture results, join the FixtureData table to an alias of itself on the FixtureID field where the TeamID in the table is not equal to the TeamID in the alias. This returns the pairs of teams each match.

With this query you have basically recreated your original structure. However the data in the underlying tables is far more manipulable for a range of purposes.

To find a list of winners return the Team whose GoalsFT is the greater.

To find a list of home game victories then take these winners and join in the Team and Fixture tables and return results where the winner's Homegrond = Venue.

Meanwhile you can easily return the stats for one team which was difficult before.

It is often easier to take each query a step at a time.
eg Return the victories for the team, then determine the Home/Away information followed by another Totals query to sum the findings.

It will take you a while to get your head around it but keep at it and you will be really pleased with your achievement.

 

[DCrake]

I have only scanned this posting and do not wish to interject with my opinions, however on a more general topic that has always intrigued me is that when you watch a game of footbal and it gets to half time, all the armchair pundits start analysisng the game, who kicked what whre he ran how many passes, how many shots. Now there are two teams out their not just one man. How the hell do they gath all that information in such a short period of time. What kind of software could do that? Surely there is not a whole barrage of geeks with click boxes and radar buttons monitoring each persons actions.

It's a bit like when you go shopping at ASDA and you buy any 3 and get the cheapest one free. How does it know you have bought two already and which one is the cheapest, especially when thet all have different barcodes and could be in different categories. I have written an EPOS in the past but it did not have all the bells and whistles they have.

David

 

[Galaxiom]

I have only scanned this posting and do not wish to interject with my opinions,

I'm not sure that is really fair. I for one would like to hear your opinions rather than wonder if someone with much more experience than I would see it entirely differently. I took the problem on because nobody else had.

One of the challenges in database design is maintaining the flexibility of mind to not career down one path without constantly considering the relative merits of different ideas. Many of us work alone without anyone to bounce ideas off. It is one of the reasons I come here and why I kept hoping someone would provide some more perspective in this thread. Given these circumstances the best we can do is to maintain the flexibility in the design to make structural changes without starting out again.

I thought quite hard about this before I made my suggestion. I am confident of what I said about normalisation.

I am also certain that the original structure placing a team in different fields of the same table is messy to query when you want to recover long term information about a team rather than just a single match outcome. The OP's difficulty in constructing these queries attests to this.

However I can see potential complexities in my idea with the data entry form (though not in presentation). To that end I have already contemplated splitting the FixtureData into home and away tables and using a union query to determine team statistics. This is an easy step at any point but I would like to persue the current idea a little further because it is interesting, though in the end I expect splitting will be the best option.

If this is where you are thinking then I am happy because we are on the same path. If the whole concept sucks because there is something major I haven't forseen then I would rather hear about it now.

 

[cybercider]

Probably best to import the data into a table in the orginal format. Import rather than link so it is read only. Access is the most powerful manipulation tool at your disposal. Use an update query to replace the Team Name with the ID. Join the imported table to the Fixture Data on Team name.

Will look into that thanks.

Can I use queries to extract the relevant info for inserting into the appropriate table? e.g. record 1 is Liverpool v Arsenal. Then using query extract the relevant Liverpool stats and insert that into my new FixtureData table, repeat for the Arsenal stats but then also ensure the correct FixtureID and ResultID is assigned to both records.

As you can my historical data is a single record for each fixture !!

   Date   HomeTeam   AwayTeam   FTHG   FTAG   FTR   HTHG   HTAG   HTR   Attendance   Referee   HS   AS   HST   AST   HHW   AHW   HC   AC   HF   AF   HO   AO   HY   AY   HR   AR

Note: I've not renamed any of the above headings to match my new structure yet.

As for the comment about obtaining stats I recall seeing a documentary a few years ago about Opta (sports statistics company), a lot information was gathered using camera's positioned around the pitch but they also had a few people stuck in a truck/portacabin watching. These people would watch the match on a monitor and record the stats manually. Likely keyboard shortcuts for each player and the action that happened I guess.

Cc

 

[DCrake]

Galaxy,

Where did all that come from? My initial comment was to state that I had not read the threads thouroughly with the intention of contributing. This is because I thought that you were doing a fine job as you had built up a retlationship with the OP. My subsequent comments were merely an observation that has always had me perplexed as to how they collate the stats during a match.

If I'd commented on your recommendations and they conflicted with your opinions then this would be understandable.

David

 

[Galaxiom]

Galaxy,
Where did all that come from?

Sorry. I think I both got and gave the wrong impression there. I certainly didn't mean it as a criticism at all, more an invitation. I took interjection to mean you would do it differently. The comment about fairness was that once suggesting an interjection might have been appropriate one would be bound to elucidate it.

Perhaps the usage of interject has a stronger meaning in au. I would have used interrupt, which carries less emotion.

Then I kind of rambled philosophically about database design after that (as I can be prone to do). This may have made it sound like I was aggressively defending my position when I was really just explaining where I was at, and letting you know I had my own reservations but they were within the scope of my design.

If I'd commented on your recommendations and they conflicted with your opinions then this would be understandable.

And that would not have bothered me in the slightest. Discussion of the relative merits of different approaches is the best way to learn.

 

[Galaxiom]

Can I use queries to extract the relevant info for inserting into the appropriate table? e.g. record 1 is Liverpool v Arsenal. Then using query extract the relevant Liverpool stats and insert that into my new FixtureData table, repeat for the Arsenal stats but then also ensure the correct FixtureID and ResultID is assigned to both records.

That describes it except you can do all the home teams in one pass. Then a similar query for the Away team results using the corresponding away columns.
Use an Append Query (as they are called in design mode, they are called INSERT in SQL)

You see how you joined the TeamID in the database. You will just do it backwards for the import query. Join on the TeamName in the import table. In the Append To box of the appropriate field you will type something like:
t_teams.TeamID

This writes the ID instead of the name. Similarly with the location of the fixture. FixtureID will be autonumbered.

But first you need to get the data into Access.

As you can my historical data is a single record for each fixture !!

   Date   HomeTeam   AwayTeam   FTHG   FTAG   FTR   HTHG   HTAG   HTR   Attendance   Referee   HS   AS   HST   AST   HHW   AHW   HC   AC   HF   AF   HO   AO   HY   AY   HR   AR

Note: I've not renamed any of the above headings to match my new structure yet.

During the import you can name the fields as you wish. One of the import questions is about the first row being the field names. They don't have to be the same as your destination fields. Don't worry, it doesn't matter that your data is in fixture format. It is amazing what Access can do to data and this hurdle is easy.

Import the historical data so you can get a table into the relationships window. Then we can sort the relationships for the conversion to the new format.

As I said to David last night I am thinking it will probably be easier to have separate home and away tables because this will make entering new data easier. I assume you will ultimately want to have a form where you entry new results each week.

But we will cross that bridge when we get to it.

All this must seem confusing but you are doing quite well. Once we get the data imported to the right format you will begin to really make some progress.

 

[Galaxiom]

Last night when I went to bed the penny dropped and I realised I had the headed down one path with blinkers on. I think I got too embroiled in database theory and couldn't see the wood for the trees.

The records can be held by fixture like the historical data and can still be searched as though they were single records for each side.

The home and away sections of the records can be recombined on the fly using a Union query. I hadn't needed to use one of these before and didn't appreciate their full capability.

It could still be done as I outlined before but the Union query technique has advantages. Everything else I said still applies. You still hold all the records such as Venue and Team and HomeStadium as IDs in the same structure and hold the names in other tables.

Second copies of the t_Teams and Stadium tables would be related to the second team in the FixtureData table.

Sorry to add to your confusion.

 

[cybercider]

"Where's the forest's edge?" asked Cc.
"Mmm?" replied the Shark.

Just so we're not losing sight my end goal which is to be able to produce a series of reports and add new match stats, for example:

Team Overall - overall stats of a specific team, totals, % e.g. home wins, draw %, goals for and against etc.

Head to Head - as above but stats from the two specific teams only

I figure best refresh that so that I can design the foundations with the end goal in mind.


Have to admit at the moment I'm struggling with this tonight but ... a single fixture table simplifies the form design and the union queries are used to create on the fly table data for my reports?

So an updated structure like attached?

Cc

 

[Galaxiom]

I did LOL about the shark.

Sorry I didn't get back to you last night. Unfortunately the site is often very sluggish at night in Australia (probably due to most users being on the other side of the planet and using it during the day). Works great here in the day but I'm at work so I only do quick answers. (Some nights I get a bit sluggish too.)

With your structure, since the main records are all back in one table (now effectively a complete fixture information table) the tFixture table can be dropped and the venue put back into the main data table. It will be an ID with a third copy of the stadium table joined. The FixtureID becomes the same as key ID field of this table so one of them can be dropped.

The OfficialID (I assume this is the Referee) would be joined to the Referee in the data table (once again stored as a number in the main table).

Fix this and I think you will have it.


A union query allows the results of two queries to be combined into one. So for gathering statistics about results we make a query of the home teams and the information we want and an equivalent query for the away teams. Then they are combined in a union to get the data into one set.

This is the capacity to collect information when the teams are located in two different fields that I had not appreciated and why I went down the blinkered path. What I had proposed would work but it would have been much more convoluted to enter new data.

It was good for me because I now understand what Union queries are really about. It was obviously a gaping hole in my knowledge. Sorry for leading you along the garden path too.

 

[cybercider]

No worries been a tad busy with real life things, thanks for the advice all the same.

Anyways, considering I'm really only after fundamentally basic statistics on the teams at this time I streamlined the database even more now. I've dropped the stadium and officials tables for now.

I have two tables (tTeams & tFixtures), joins from the tTeams to fields HomeTeam and AwayTeam in tFixtures.

So I have two queries now (qHomeStats & qAwayStats), each has the corresponding stats in it. Union whilst I examined many examples and understand it to a degree I'm not sure how to go about comparing the one teams goals against another to produce the winner and then count that number.

Cc

 

[Galaxiom]

Your two queries should also return a field for the margin. If it was won by the team then it will be positive, negative if lost and zero if a draw.
The Margin field expression for the Home team query will be:

Iff(Fixture.Venue = Team.HomePitch, FTSHome - FTSAway, FtsAway - STSHome)

The reverse for the Away team query. You will retain more information this way rather than using a Win Y/N field which would also have ruble with a draw. You will also be able to return wins above or below certain margins in later queries.

It should also show if it was a home match.
The expression is:

Iff(Fixture.Venue = Team.HomePitch,True, False)

Then unionise the queries to combine the results for all teams in the one set of fields.
MatchID, TeamID, Home(Y/N), Margin, FTScore, HTScore, etc

Then you can query this data for anthing you want.
Home wins for a for a particular team between two dates:

WHERE TeamID = InputTeam AND Margin > 0 AND Home = True AND MatchDate BETWEEN Date1 and Date 2

And a field to sumarise every team:
GROUP BY TeamID

Yes/No fields hold the values as -1 for true and 0 for false. They can simply be summed in a Totals query.
Use the expression ABS(SUM(fieldname)) to return a field that shows the number of those records.

For example after selecting the records you need with WHERE or HAVING clauses you can easily total the number of Home wins.