Madden League database (1 Viewer)

[bdhtexas]

I run a Madden online league (Nintendo Wii)

I am stuck on how to run a query or report that calculates STANDINGS.

W - L - PCT - GB - PF - PA - DIV WL - CONF WL

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

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 have Access 2007.

Any help that nudges me in the right direction would be greatly appreciated.

PS - I haven't used Access in 2 and half years, and that was Access 97 where I used to work. So I am a bit rusty.

 

[ajetrumpet]

what do you want the qry to look like!? that table system you are showing us? or is that the query you already have created? nice description, but you haven't asked anything really. STANDINGS? like, ranking of players? HOW? win to loss ratio? division win to loss? conference win to loss? by another field perhaps? by how many pts scored?

remember, not all of us are sports fanatics. tell them exactly what you need, and you usually get the help needed. be vague, and your thread will enjoy a wonderful life floating in outerspace of cyberspace.

 

[bdhtexas]

ok, I will try to be more specific...

What I am trying to do is create a query that is like Standings:

PLAYER NAME - WINS - LOSSES - PF - PA

I would be happy with just that, but anything else is just a plus.

Thanks

 

[bdhtexas]

Here are the fields on my form:
My structure may be off and that may be the problem, if so let me know and I will fix it.

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

 

[ajetrumpet]

i'm getting the impression that your data is not normalized at all. upload the database, and ifit doesn't take the whole day to figure out, i'm sure someone here will be able to provide you a solution to the problem here


for me, it's hard to invision it just reading the posts

 

[bdhtexas]

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-league-standings-f34/any-team-league-standings-t735.htm

The second league standings can be found here http://hmfl.forumotion.com/draft-league-standings-f35/draft-league-standings-t736.htm