Microsoft Access beginner question

[bigboy]

Hey folks, new poster with a question.

I would like to create a detailed database of college football players and their playing history(stats, etc.), for study purposes. The idea was to have a table of all the players's names, and then link to a new table for each of their seasons in a new table. From there, I can view more detailed statistics about their seasons.

Here's a more detailed version of what I want:

Main table: Player information(First, last, school), Freshman Season(Link), Sophomore Season(Link), Junior Season(Link), Senior Season(Link).

Freshman Season(Position, season stats, season game log(link)), and so on.

I think I have to do this using some kind of the relationships feature. The end result is easy sorting to find out hidden statistics about players. Yeah, it's a complicated task but it's something I'd enjoy doing if Access can do it. Anyone recommend any videos or links to tutorials that talk about this kind of subject?



Also, I know there are online databases that already do this, but I want my own database for offline use and opportunities to add more data that I want to see in the future.

Thank you!

[June7]

Sould not be a separate table for each season, just one table with another field for season.

[bigboy]

I understand your suggestion(Freshman season column contains field of statistics, Sophomore season column contains field of statistics, etc), but I'd like the player's season statistics to be in their own table of some kind for sorting purposes, so I can compare for instance, player 1's freshman statistics with player 2's season statistics. Makes sense?

As far as I know, there's no kind of button link/object of some kind that allows communication like this, so it may be a bit complicated...

[June7]

It is possible to compare data of records in same table - subqueries.

Compare player 1 freshman with player 2 freshman? That is in one table.

No, my suggestion is not for freshman column, sophomore column, etc.

Player stats db topic of several threads - have you searched?

[ArviLaanemets]

I understand your suggestion(Freshman season column contains field of statistics, Sophomore season column contains field of statistics, etc)

You didn't understand June7's suggestion at all!

At start, you have to understand that with access, there are features to store and organize data (tables, indexes, etc.), and features to edit and view data (forms, reports, queries). and all of those are good at what they are meant for, and not so good or even not applicable for other tasks. So when starting a new database, you have to design data structure which is best for this database, then you design tables according with this structure - and after that you can forget about tables (unless you discover the current structure is not working and you need to made some changes). From this point on forms and reports take over. Forms guarantee, that right data are stored to tables, that user sees exactly this info what is needed to make right decisions, etc. And reports collect all needed info from tables and print or display this info in any needed format. And to be able to do all this, the tables and their structure have to support interface - the info must be easily read from tables for whatever is needed.

To return to June7's suggestion, I think this was meant something like:
tblPlayers: PlayerID, FirstName, LastName, School, ...
tblPlayerSeasons: PlayerSeasonID, PlayerID, SeasonYear, PlayerClass, ... (where PlayerClass will be freshman or sophomore or junior or senior)

You can then design e.g. an unbound main form where you select a year in combo box, and all players registered for this year are displayed in contionous subform. You select e.g. "sophomore" in second combo, and the filter is set on subform, so only sophomore players available this year are displayed, etc. For more complex cases, you need forms based on queries, which can combine data from several tables.

Further you need a tables
tblTeams: TeamID, TeamName, ...
tblTeamPlayers: TeamPlayerID, SeasonID, TeamID, PlayerID, PlayerPosition, ...
tblSeasonGames: SeasonGameID, SeasonID, HomeTeamID, GuestTeamID, HomeScore, GuestScore, ...
tblSeasonPlayerGames: SeasonPlayerGameID, SeasonGameID, TeamPlayerID, ...
tblSeasonPlayerStatistics: SeasonPlayerStatisticID, SeasonPlayerGameID, StatisticType, StatisticValue, ...
e.t.c.

Edit. When you register game player statistics, then some of them are numeric (e.g. number of passes), but some may be texts. In such cases it is best to use numeric values anyway - but in form you use combo box which selects numeric value, but displays according text instead. As example we can use field PlayerCalss in tblPlayerSeasons. You can set values for player class as 1 - freshman; 2 - sophomore; 3 - junior; 4 - senior. You select player class from combo, and you see class texts, but numeric value is stored into table.