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_Crosstab 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_Crosstab. The last thing I did was to create another query called WinPercentage and added the Winners_Crosstab and Loosers_Crosstab 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)*100

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]

Sorry, forgot to mention that I'm using Access 2003 and Windows XP professional.

[MMcCarthy]

You need to change the crosstabs so all teams appear even if there are 0 winners or 0 losers. If you want to post the SQL of the crosstab queries I'll have a look at them.

[G8tors]

This is the Crosstab Query I have set up to count the number of superbowl wins for each team.
TRANSFORM Count([SB Wins].[W/L]) AS [CountOfW/L]
SELECT [SB Wins].[Fantasy Team], Count([SB Wins].[W/L]) AS [Total Of W]
FROM [SB Wins]
GROUP BY [SB Wins].[Fantasy Team]
PIVOT [SB Wins].Season;

This is the Crosstab Query I have set up to count the number of superbowl losses for each team.
TRANSFORM Count([SB Losses].[W/L]) AS [CountOfW/L]
SELECT [SB Losses].[Fantasy Team], Count([SB Losses].[W/L]) AS [Total Of L]
FROM [SB Losses]
GROUP BY [SB Losses].[Fantasy Team]
PIVOT [SB Losses].Season;

SB Wins query is setup with the W/L field criteria set to "W" and the SB Losses query is the setup with the W/L field criteria set to "L".

Thank you for taking the time to help me.

[MMcCarthy]

OK the problem here is you have your wins and losses in separate tables. You will need to join both queries to the table which contains all the Fantasy team names. For this example I will use the name Teams for the table. Now change the crosstabs as follows:

TRANSFORM nz(Count([SB Wins].[W/L]),0) AS [CountOfW/L]
SELECT [Teams].[Fantasy Team], nz(Count([SB Wins].[W/L]),0) AS [Total Of W]
FROM [Teams] LEFT JOIN [SB Wins]
ON [Teams].[Fantasy Team]=[SB Wins].[Fantasy Team]
GROUP BY [Teams].[Fantasy Team]
PIVOT [SB Wins].Season;
 

TRANSFORM nz(Count([SB Losses].[W/L]),0) AS [CountOfW/L]
SELECT [Teams].[Fantasy Team], nz(Count([SB Losses].[W/L]),0) AS [Total Of L]
FROM [Teams] LEFT JOIN  [SB Losses]
ON [Teams].[Fantasy Team]=[SB Losses].[Fantasy Team]
GROUP BY [Teams].[Fantasy Team]
PIVOT [SB Losses].Season;
 

[G8tors]

Thanks MMCCARTHY. I haven't tried your code yet but I wanted to let you know that after I read your post about having all the teams appear that made sense. So I deleted the querries I made that seperated the superbowl winners and the super bowl losers (SB Wins and SB Losses) and created a query that contains both wins and losses called SB Games. I then just started trying things and came up with this crosstab query:

TRANSFORM Sum([SB Games].[W/L]="W")*(-1) AS CountOfW
SELECT [SB Games].[Fantasy Team], Sum([SB Games].[W/L]="W")*(-1) AS [Total Of W]
FROM [SB Games]
GROUP BY [SB Games].[Fantasy Team]
PIVOT [SB Games].Season;

The numbers were negative so I multiplied them by -1 and that seemed to work accept for one annoying problem. zeros show up with a negative sign in front of them on my report (ie -0). I did this before I saw your last post. Am I bettter off going with your newest code or do you know how to keep the zeros from showing up as negative zero?


Thanks again

[G8tors]

I went ahead and did what you suggested and it worked great. However, when I create my final query:

SELECT [SB Wins_CrossTab2].[Total Of W], [SB Losses_CrossTab2].[Total Of L], [SB Wins_CrossTab2].[Fantasy Team], [Total Of W]/([Total Of W]+[Total Of L])*100 AS [win%]
FROM [SB Wins_CrossTab2] INNER JOIN [SB Losses_CrossTab2] ON [SB Wins_CrossTab2].[Fantasy Team] = [SB Losses_CrossTab2].[Fantasy Team]
ORDER BY [Total Of W]/([Total Of W]+[Total Of L])*100 DESC;

I get an overflow error. I supsect that is because I have teams that have never been in the superbowl and it is dividing by zero to calculate the win%?

We are close.

[MMcCarthy]

Change it to:

SELECT [SB Wins_CrossTab2].[Total Of W], [SB Losses_CrossTab2].[Total Of L], [SB Wins_CrossTab2].[Fantasy Team], [Total Of W]/nz([Total Of W]+[Total Of L],1)*100 AS [win%]
FROM [SB Wins_CrossTab2] INNER JOIN [SB Losses_CrossTab2] 
ON [SB Wins_CrossTab2].[Fantasy Team] = [SB Losses_CrossTab2].[Fantasy Team]
ORDER BY [Total Of W]/([Total Of W]+[Total Of L])*100 DESC;
 

This will force a divide by 1 when no records found.

[G8tors]

I still get the overflow error. Can we not have zero in the numerator also?

[MMcCarthy]

Try this ...

SELECT [SB Wins_CrossTab2].[Total Of W], [SB Losses_CrossTab2].[Total Of L], [SB Wins_CrossTab2].[Fantasy Team], [Total Of W]/nz([Total Of W]+[Total Of L],1)*100 AS [win%]
FROM [SB Wins_CrossTab2] INNER JOIN [SB Losses_CrossTab2] 
ON [SB Wins_CrossTab2].[Fantasy Team] = [SB Losses_CrossTab2].[Fantasy Team]
ORDER BY [Total Of W]/nz([Total Of W]+[Total Of L],1)*100 DESC;
 

[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.09090909090909
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.52380952380952
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)*100.

[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.09090909090909
Drunken Sailors 3 3 9.09090909090909
Ever Spewing Vomit Dogs 1 1 9.09090909090909
Fangs 2 2 9.09090909090909
Flying Monkeys 2 2 9.09090909090909
I M Toast 1 1 9.09090909090909
Jawjackers 1 1 9.09090909090909
Madcaps 2 2 9.09090909090909

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

[G8tors]

I think that is the same code you gave me before. When I go to design mode and set win% to sort descending, I get the parameter windows that pop up and then when it runs it doesn't sort.

[MMcCarthy]

I think that is the same code you gave me before. When I go to design mode and set win% to sort descending, I get the parameter windows that pop up and then when it runs it doesn't sort.

I assume if you use order by [win%] it doesn't work.

I'm afraid it just might not be possible to set the order by in the query. Where are you going to use the query?

[MMcCarthy]

OK, one last try ...

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]
ORDER BY Sum(IIf([SB Games].[W/L]="W",1,0)) / NZ(( Sum(IIf([SB Games].[W/L]="W",1,0))+ Sum(IIf([SB Games].[W/L]="L",1,0))),1)*100
 

[G8tors]

YOU DID IT!!! THANK YOU THANK YOU THANK YOU THANK YOU.

Its working now. I have the information going to EXCEL and then I copy the information into a web page. My next project will be to figure out how to generate a web page directly from the information from the DB. I tried creating an .ASP page but it takes about 2 minutes to load all the data and thats way too long. One problem solved though.

Thank you so much.

Scott

[MMcCarthy]

YOU DID IT!!! THANK YOU THANK YOU THANK YOU THANK YOU.

Its working now. I have the information going to EXCEL and then I copy the information into a web page. My next project will be to figure out how to generate a web page directly from the information from the DB. I tried creating an .ASP page but it takes about 2 minutes to load all the data and thats way too long. One problem solved though.

Thank you so much.

Scott

You're welcome Scott. Glad its working.

[G8tors]

Mary,

Is there a way to replace the NZ with an IIF function because when I try to pull the data from within Excel I get a "Undefined Function 'NZ' in Expression."

[MMcCarthy]

Change

NZ([Total Of W]+[Total Of L],1)*100

to

IIf([Total Of W]+[Total Of L]=0,1,[Total Of W]+[Total Of L])*100

and change

NZ((Sum(IIf([SB Games].[W/L]="W",1,0))+ Sum(IIf([SB Games].[W/L]="L",1,0))),1)*100

to

IIf(Sum(IIf([SB Games].[W/L]="W",1,0))+Sum(IIf([SB Games].[W/L]="L",1,0))=0,1,Sum(IIf([SB Games].[W/L]="W",1,0))+Sum(IIf([SB Games].[W/L]="L",1,0)))*100

[G8tors]

OK that worked.

There was no way I could have done this by myself.

Thanks again

[MMcCarthy]

OK that worked.

There was no way I could have done this by myself.

Thanks again

You're welcome.