-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Your design of the Games table is incorrect. It should be like this:
CREATE TABLE Games (
game_nbr INT NOT NULL ,
team_name CHAR(5) NOT NULL -- just use initials "USC", "UCLA", etc.
REFERENCES Teams (team_name)
ON DELETE CASCADE ,
final_score INT NOT NULL ,
game_length DATETIME NOT NULL ,
CHECK (game_length BETWEEN #00:01# And #23:59#)
overtime_length DATETIME NULL -- NULL means no overtime
CHECK (overtime_lengt h BETWEEN #00:01# And #23:59#),
CONSTRAINT PK_Games PRIMARY KEY (game_nbr, team_name)
)
The game_length & overtime_length columns would hold only times, not
dates.
If you were using overtime as a Yes/No field, then you don't need it.
Any value, other than zero, in the overtime_length indicates there was
overtime played.
The CHECK constraints are for illustration only. They are the column's
(Access calls them Fields) Validation property.
If you want to keep track of games over more than one season add a
season_year colum to the table and make it part of the Primary Key.
E.g.:
....
season_year INT NOT NULL
CHECK (season_year 1959)
....
CONSTRAINT PK_Games PRIMARY KEY (game_nbr, team_nbr, season_year)
Your query doesn't match the criteria you describe in your post - it
just shows the teams & there final scores - no percentages, etc. Since
football defense & offense changes according to who has possession of
the ball you'd have to have more detail, probably another table, showing
the changes between offense & defense per team & the scoring while each
team was in each role. Usually, a team only scores when in the
offensive role. By offense & defense, do you mean visiting team and
home team, respectively? If so you can add another column to the table
indicating their role "Visitor" or "Home." E.g.:
team_role CHAR(1) NOT NULL CHECK team_role IN ('V', 'H') ,
It would also be part of the Primary Key, just add the column_name to
the others in the PK constraint definition.
To find out scores for USC when they were Visitors, sorted highest to
lowest, you'd have a query like this:
SELECT game_nbr, final_score
FROM Games
WHERE game_name = "USC"
AND team_role = "V"
ORDER BY final_score DESC
--
MGFoster:::mgf0 0 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBRLF154echKq OuFEgEQJB1QCff/wgoyb2TLpugn5AS FzPg6HQZ/8AoNGc
UQMbzvFSr6K/QQIGuIm67VKS
=BdCs
-----END PGP SIGNATURE-----
mm*******@usfam ily.net wrote:
I have a query in Access 2000 like this:
SELECT [games].[team1], [games].[points team1], [games].[length],
[games].[overtime], [games].[overtime length], [games].[team2],
[games].[points team2]
FROM games
WHERE team1="USC" or team2="USC";
This is based on a game I played years ago. We created "tournament s" where
the top college football teams
of the 1960, would play each other.
The games had different lengths depending on what round the games were in
(shorter games with all 32 teams
playing and longer when the 16 teams etc.). In addition some of the games
went to overtime.
We sorted them by hand by percantage, how few points the "defense" of the
team would score and then
by how many the "Offense scored"
What I would like to do, is somehow work the query so that I don't have to
worry if the team is "team1" or
"eam2". I also want to sum all the points the defense allowed so I can find
a stat ("I call Points allowd per minutes). I also want to sum all the
offense points from all as well.
Can I get this information with one query? It could save me on writing CPP
code.
