Football Coaching Questions

[maldrich]

I am new to this forum, but reading it has helped immensly so far. I have gotten a lot of tips that have helped.

I am not an Access programmer, but have used it for quite a while as a database. I am a football coach (US football) and a lot of what we do is based on tendencies derived from scouting other teams. I have been playing with Access all summer trying to get it to help me and I am having troubles.

I am trying to get Access to kick out reports that give %'s of totals. For example, if there are 70 plays in a game, they are either run plays or pass plays. I am trying to get Access to give me the % of run or pass in different situations.

If there are 10 1st Down plays, and 6 are runs and 4 are passes, then on 1st Down, the opposing team is 60% Run. That easy enough on paper, but getting Access to do it is something else. The field for Run/Pass is a text field, so there is a count involved. I saw how to count in a report footer, but how can I get it to display as a percentage?

I have a million questions on this and would love to have Access do everything for us. We currently use it as a Recruiting database and it works awesome.

Any help would be appreciated. This seems complex to me, but maybe it's not. Any one that is willing to help us with our troubles and setting up our database and reports would be greatly appreciated. Maybe we could send you a hat and some t-shirts or something.

Thanks.

-Mike Aldrich
maldrich67@yahoo.com

 

[andrew93]

Hi Mike
I'm going to have to make some assumption here, so assuing your report is based on a query (say 'qry_Stats') and your field on your report (and in your query) is called something like 'RunOrPass' then the percentage of run plays over all plays would be something like this :

=DCount("[RunOrPass]","qry_Stats","[RunOrPass] = 'Run' ") / Count([RunOrPass])

formatted as a percentage. Be sure to use your actual field and query names.

I trust I haven't over-simplified the problem.

HTH, Andrew

 

[Rich]

Have you tried using the Report wizard? It can include many calculated controls

 

[ScottGem]

Mike,
A percentage is obtained simply by divding the total number of something into the filtered number of that something. So you would divide the total number of plays into the number of running plays to get that percent. That's the simple part.

The real key here is going to be your database design. I would envision a design like so:

tblGames
GameID (PK Autonumber)
HomeTeamID (FK)
VisitingTeamID (FK)
GameDate
LocationID (FK)

tblTeams
TeamID (PK Autonumber)
TeamName

tblPlays
PlayID (PK autonumber)
GameID (FK)
TeamID (FK)

tblPlayData
PlayDataID (PK Autonumber)
PlayID (FK)
PlayTypeID (FK)

tluPlayTypes
PlayTypeID
PlayType (ex Run, First Down, etc.)

With a structure like that you can categorize every play in various ways and get your statisitics about each game or team.

 

[maldrich]

Andrew93 response

=DCount("[RunOrPass]","qry_Stats","[RunOrPass] = 'Run' ") / Count([RunOrPass])

formatted as a percentage. Be sure to use your actual field and query names.

Andrew, Would that statement quoted above then be listed in the report footer?

 

[maldrich]

Scott-
Forgive my thick head and lack of intimate knowledge of Access, but I am trying to interpret your symbols. Please let me know if I'm on base or off.
tbl= New Table
PK autonumber=Primary Key, Autonumber
FK=??? Not sure on this one. However it looks like it is the ID which will link or create a relationship with the other tables, I think?

Please let me know if I am off base.

 

[ScottGem]

Nope you nailed it. You might want to do a searching on Access naming conventions for more. The idea is to name things like tables, forms, reports, queries, controls, etc. in a way that identifies their type or function. So I use tbl for data tables, tlu for lookup tables and tjx for join tables.

PK is primary key, FK is Foreign key. A Foreign key is your join field. It contains the value of the primary key of the related record. I follow the custom of naming my FKs the same as my PKs. There are sometimes exceptions though as in the case of Home and Visiting teams. You only want one Teams table, so you need to differentiate.

But the real important thing is that your tables are designed correctly. I was just guessing here from my knowledge of football, databases and statistics. But it should give you an idea of how to proceed.

 

[maldrich]

Thanks Scott. I'll let you know how it goes.
But I am much farther along with all the help.

-Mike

 

[ScottGem]

Good luck. Glad to assist. I'm curious though, on what level do you coach?

 

[andrew93]

Andrew, Would that statement quoted above then be listed in the report footer?

I haven't read the other posts in this thread in any detail, so in the event it is still relevant then 'yes' it would go in the report footer. Sorry for not clarifying that....
A

 

[maldrich]

Scott-

I am a Division II college football in coach in South Dakota.

 

[ScottGem]

Thanks for answering. Let us know if we can help any further.