need help with query

[G8tors]

I have a fantasy football league that I am keeping stats for in an Access Database. I need help coming up with a way to calclulate the winning % for each team for their entire career. What I have is a table with the following fields:
Fantasy Team
Season
Week
W/L
Own Score
Vs
Opponent
Opponent Score
Game type

The W/L field will either have a W or a L depending if they won or loss, the Vs field will either have a vs or a @ depending if its a home or away game, and the Game Type field tells if it was a regular season game, playoff game, Super bowl game, or Toilet bowl game.

What I have done so far is I created a query on the above table to sort out just the winners and this new table is called Winners. I then created a crosstab query called Winners_Crossta b that counts the number of Wins each team has had for each season. So now I have totals wins. I then did the same thing for losses to end up with a query called Loosers_Crossta b. The last thing I did was to create another query called WinPercentage and added the Winners_Crossta b and Loosers_Crossta b and linked the two tables by Fantasy team. The fields include:
Fantasy Team
Count of Wins
Count of Losses
Win%
The win% field is one I added manually and is a calculation of
wins/(wins+losses)*1 00

This works fine except for when a team has no wins or no losses. If a team went 14-0 then the team doesn't show up in the WinPercentage query. I understand why it doesn't but I don't know how to get around this. I hope I made this clear enough for you to give me some quidance. I am new to databases and just started learning Access about 4 months ago.

Thanks for your help,

Scott

[G8tors]

Shoot, that didn't work either. Still getting overflow error.

I definetly owe you a few beers after this is all done.

[MMcCarthy]

Shoot, that didn't work either. Still getting overflow error.

I definetly owe you a few beers after this is all done.

Leave the OrderBy off for the moment and see if there is still an error.

[G8tors]

OK the query runs now but math isn't right and some win% fields say #Error

Total Of W Total Of L Fantasy Team win%
1 1 Annihilators 9.0909090909090 9
0 0 Ballbusters #Error
0 0 Bean Counters #Error
0 0 Big Johnson #Error
0 0 DandBChampions #Error
0 0 Dirt Farmers #Error
2 1 Drunken Sailors 9.5238095238095 2
0 1 Ever Spewing Vomit Dogs 0
2 0 Fangs 10
0 0 Fighting Amish #Error
.....

[MMcCarthy]

OK lets go back to the beginning. I don't think you need the crosstab queries as the base for this query. Try running this query instead.

SELECT [SB Games].[Fantasy Team], Count([SB Games].[W/L]="W") AS [Total Of W], Count([SB Games].[W/L]="L") AS [Total Of L], [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
FROM [SB Games]
GROUP BY [SB Games].[Fantasy Team]
ORDER BY [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 DESC;
 

[G8tors]

I made the changes you suggested and when I run the query, a "Enter Parameter Value" window pops up and asks me for Total of W and once I enter a number and hit ok another "Enter Paramater Value" window asks me for Total of L.

The result, no matter what numbers I enter, is:

Fantasy Team Total Of W Total Of L win%
Madcaps 2 2 50
Jawjackers 1 1 50
I M Toast 1 1 50
Flying Monkeys 2 2 50
Fangs 2 2 50
Ever Spewing Vomit Dogs 1 1 50
Drunken Sailors 3 3 50
Annihilators 2 2 50

Can you use IF ELSE statements is SQL? For example, in the above code where we were getting the overflow error, if wins + losses = 0 then win % = 0 else win% = wins/(wins+losses)*1 00.

[MMcCarthy]

Try this...

SELECT [SB Games].[Fantasy Team], 
NZ(Count([SB Games].[W/L]="W"),0) AS [Total Of W],
NZ(Count([SB Games].[W/L]="L"),0) AS [Total Of L], 
[Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
FROM [SB Games]
GROUP BY [SB Games].[Fantasy Team]
ORDER BY [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 DESC;
 

If that doesn't work remove the orderby line as that is probably the problem. If that solves it we will explore some options for the orderby line.

[G8tors]

the parameter window still comes up for both wins and losses and numbers don't look right.

When I removed the Oder By line it runs without the parameter window opening up but the numbers are still not right.

Fantasy Team Total Of W Total Of L win%
Annihilators 2 2 9.0909090909090 9
Drunken Sailors 3 3 9.0909090909090 9
Ever Spewing Vomit Dogs 1 1 9.0909090909090 9
Fangs 2 2 9.0909090909090 9
Flying Monkeys 2 2 9.0909090909090 9
I M Toast 1 1 9.0909090909090 9
Jawjackers 1 1 9.0909090909090 9
Madcaps 2 2 9.0909090909090 9

Results should be:
Annihilators 1-1
Drunken Sailors 2-1
ESVD 0-1
Fangs 2-0
Flying Monkeys 2-0
I M Toast 0-1
Jawjackers 0-1
Madcaps 0-2

It looks like it is adding the two numbers and then using that number for both wins and losses.

[MMcCarthy]

Try this...

SELECT [SB Games].[Fantasy Team], 
Sum(IIf([SB Games].[W/L]="W",1,0)) AS [Total Of W],
Sum(IIf([SB Games].[W/L]="L",1,0)) AS [Total Of L],
[Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
FROM [SB Games]
GROUP BY [SB Games].[Fantasy Team]
 

[G8tors]

Thats almost working, but I can't sort the Win% field.

When I try it with desceding Win% the parameter window pops up again.

Do you know of any links that talk about the sum iif statement and nz so I can learn more about that? I'm going to look in the articles section again.

[MMcCarthy]

OK put the code in the query without the Order by. Switch to Access Design view and set win% to descending.

SELECT [SB Games].[Fantasy Team], 
Sum(IIf([SB Games].[W/L]="W",1,0)) AS [Total Of W],
Sum(IIf([SB Games].[W/L]="L",1,0)) AS [Total Of L],
[Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
FROM [SB Games]
GROUP BY [SB Games].[Fantasy Team]
 

As for the functions used

I used sum with an IIf statement returning either 1 or 0 as it seems to give a better result.

NZ(condition, value) simply evaluates a condition and if null or 0 it will return whatever is in as value otherwise it returns the result.

Mary