Database layout issues

[jbessling]

I am new to Access and I am trying to build a DB for fantasy football stats. I created a table labeled GAMES, and I have these fields:
Season, week#, home team, home team points, away team, away team points, score, winner

I also have another table for TEAMS, so that the team names are referenced in a drop-down menu on the GAMES table.

The issue(s) that I am running into are that I want to be able to sort each week of each season and filter out the team with the Highest Point Value that week overall. In addition, I would like to be able to tally up the number of weeks that each team had the Overall Highest Point Total. Or for that matter, can I create a team's season record from this table? I would like to then display all of the teams' records created from this database. That way I could compare records, win-streaks, lose streaks, home record, away record, etc.

I would also like to attach the point total each week to their respective team. But sometimes the team is an away team and sometimes they are home. How can I (if this is the proper way to do it) make a relation with two fields in the same table. For instance, I want to see how many points the "Gridiron Grunts" have scored in a given season, then how do I associate the [Home Team] field with the [Home Team Points] in the same table?

Perhaps I need to be creating more tables? If someone has a template of something similar to this, I would love to get a copy. I don't know how to deal with head-to-head scoring and records. Its completely different than all the help options give which are primarily "Customer A" buy "Product X" "X amount of times", etc....

Sorry I have asked so many questions in one posting. But I think if I can learn to do a couple of these calculations then the rest should follow similar guidelines.

Thanks so much for any advice!

Jeff

[June7]

Normalizing the score data will make some analysis easier. There would be two records for each game.

tblScores
GameID
TeamID
Side (home, away)
Points

What are the score and winner fields for? Can't those be determined by the Points? This normalization does make it harder to compare the point values for each game but it is easier to add up points by team.

So if this restructuring is not desirable, then use of UNION query is one way to do the summary calcs you describe. A UNION essentially results in a data structure like the normalization I describe. There is no designer or wizard for UNION, must type into the SQL view of query designer. Like:

SELECT Season, [week#], "Home" As Side, [home team] As Team, [home team points] As Points, score, winner FROM table
UNION SELECT Season, [week#], "Away", [away team], [away team points], score, winner FROM table;

Now can use this query as source for the summary calcs.

BTW, advise not to use special characters, spaces, punctuation (underscore is exception) in names. Better would be weekNum, home_team.

[dblife]

I agree with June7 - rather than a column [field] for home and away, have one column for side and record a value for 'h' or 'a' which could be a foreign key or just a value you expand on later with an iif statement.

[dblife]

great project - let me know if you want any further help