I use Access 2007
I run a Madden online league (Nintendo Wii)
I am stuck on how to run a query or report that calculates STANDINGS and I think my Tables might be set up wrong.
Here are examples of 3 games played:
bdhobbs1 (Colts) 24 at Zygon (Cowboys) 34 - Feb 25th at 2:15pm
salomii (Jaguars) 35 at MJALLDAY (Jets) 38 - Feb 27th at 3:00pm
dearnest (Titans) 28 at pete36z (Eagles) 10 - Feb 28th at 11:00pm
Members can choose one of 32 teams (parenthesis) to use in an online game against another member. They post the final after their game is completed in our forum.
This is the information about each game, currently I have to manually update an Excel Spreadsheet to get the Standings for the league.
If you are interested, non-members can view the standings on my league website, the address is: http://hmfl.forumotion.com/any-team-...dings-t735.htm
The second league standings can be found here http://hmfl.forumotion.com/draft-lea...dings-t736.htm
Here are the fields on my form:
Season - a number representing what season the game was played in, 1, 2, 3, etc
Type - what type of game was it, tournament, league, etc
Week - a number representing what week during the season the game was scheduled in, 1, 2, 3, etc
cdn - c is for conference game, d is for divisional game, cd is for both a conference and divisional game and n is for a non-conference game
Winner Home/Away - Home or Away goes here, based on the schedule, must be opposite of Loser Home/Away
Winning Team - The name of the team used by the winning player, one of 32
Winning Player - The name of the player who won the game
Winning Score - The score made by the winning player
Loser Home/Away - Home or Away goes here, based on the schedule, must be opposite of Winner Home/Away
Losing Team - The name of the team used by the losing player, one of 32
Losing Player - The name of the player who lost the game
Losing Score - The score made by the losing player
Date - The date the game was posted as a final
Time - The time the game was posted as a final
I have separate tables for each field, except for winning score, losing score, date and time
I have one table for both Winning Player and Losing Player
I have one table for both Winning Team and Losing Team
I have one table for both Winner Home/Away and Loser Home/Away
The Standings format is as follows:
RANK - PLAYER NAME - TEAM NAME - W - L - PCT - GB - PF - PA - DIV WL - CONF WL - DLGP
W - Wins
L - Losses
PCT - Winning Percentage
GB - Games Behind
PF - Points Scored
PA - Points Allowed
DIV WL - Divisional Games Won Lost
CONF WL - Conference Games Won Lost
DLGP - Date Last Game Played
I have a Table that lists all the players, but I have two separate fields in my table for Winners and Losers, maybe that's a problem.
With the current setup I can run cross tab queries to obtain the number of wins a player has and his points, as well as number of losses and points. But I get bogged down with errors when I try to combine them in a Standings format. (both queries and report errors)
I also posted this in queries, but I wanted to post it here for those who don't check that section.
I run a Madden online league (Nintendo Wii)
I am stuck on how to run a query or report that calculates STANDINGS and I think my Tables might be set up wrong.
Here are examples of 3 games played:
bdhobbs1 (Colts) 24 at Zygon (Cowboys) 34 - Feb 25th at 2:15pm
salomii (Jaguars) 35 at MJALLDAY (Jets) 38 - Feb 27th at 3:00pm
dearnest (Titans) 28 at pete36z (Eagles) 10 - Feb 28th at 11:00pm
Members can choose one of 32 teams (parenthesis) to use in an online game against another member. They post the final after their game is completed in our forum.
This is the information about each game, currently I have to manually update an Excel Spreadsheet to get the Standings for the league.
If you are interested, non-members can view the standings on my league website, the address is: http://hmfl.forumotion.com/any-team-...dings-t735.htm
The second league standings can be found here http://hmfl.forumotion.com/draft-lea...dings-t736.htm
Here are the fields on my form:
Season - a number representing what season the game was played in, 1, 2, 3, etc
Type - what type of game was it, tournament, league, etc
Week - a number representing what week during the season the game was scheduled in, 1, 2, 3, etc
cdn - c is for conference game, d is for divisional game, cd is for both a conference and divisional game and n is for a non-conference game
Winner Home/Away - Home or Away goes here, based on the schedule, must be opposite of Loser Home/Away
Winning Team - The name of the team used by the winning player, one of 32
Winning Player - The name of the player who won the game
Winning Score - The score made by the winning player
Loser Home/Away - Home or Away goes here, based on the schedule, must be opposite of Winner Home/Away
Losing Team - The name of the team used by the losing player, one of 32
Losing Player - The name of the player who lost the game
Losing Score - The score made by the losing player
Date - The date the game was posted as a final
Time - The time the game was posted as a final
I have separate tables for each field, except for winning score, losing score, date and time
I have one table for both Winning Player and Losing Player
I have one table for both Winning Team and Losing Team
I have one table for both Winner Home/Away and Loser Home/Away
The Standings format is as follows:
RANK - PLAYER NAME - TEAM NAME - W - L - PCT - GB - PF - PA - DIV WL - CONF WL - DLGP
W - Wins
L - Losses
PCT - Winning Percentage
GB - Games Behind
PF - Points Scored
PA - Points Allowed
DIV WL - Divisional Games Won Lost
CONF WL - Conference Games Won Lost
DLGP - Date Last Game Played
I have a Table that lists all the players, but I have two separate fields in my table for Winners and Losers, maybe that's a problem.
With the current setup I can run cross tab queries to obtain the number of wins a player has and his points, as well as number of losses and points. But I get bogged down with errors when I try to combine them in a Standings format. (both queries and report errors)
I also posted this in queries, but I wanted to post it here for those who don't check that section.
