By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 975 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

need help with query

P: 15
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
Oct 10 '07 #1
Share this Question
Share on Google+
28 Replies


P: 15
Sorry, forgot to mention that I'm using Access 2003 and Windows XP professional.
Oct 10 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 11 '07 #3

P: 15
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.
Oct 11 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM nz(Count([SB Wins].[W/L]),0) AS [CountOfW/L]
  2. SELECT [Teams].[Fantasy Team], nz(Count([SB Wins].[W/L]),0) AS [Total Of W]
  3. FROM [Teams] LEFT JOIN [SB Wins]
  4. ON [Teams].[Fantasy Team]=[SB Wins].[Fantasy Team]
  5. GROUP BY [Teams].[Fantasy Team]
  6. PIVOT [SB Wins].Season;
  7.  
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM nz(Count([SB Losses].[W/L]),0) AS [CountOfW/L]
  2. SELECT [Teams].[Fantasy Team], nz(Count([SB Losses].[W/L]),0) AS [Total Of L]
  3. FROM [Teams] LEFT JOIN  [SB Losses]
  4. ON [Teams].[Fantasy Team]=[SB Losses].[Fantasy Team]
  5. GROUP BY [Teams].[Fantasy Team]
  6. PIVOT [SB Losses].Season;
  7.  
Oct 11 '07 #5

P: 15
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
Oct 11 '07 #6

P: 15
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.
Oct 11 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Change it to:

Expand|Select|Wrap|Line Numbers
  1. 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%]
  2. FROM [SB Wins_CrossTab2] INNER JOIN [SB Losses_CrossTab2] 
  3. ON [SB Wins_CrossTab2].[Fantasy Team] = [SB Losses_CrossTab2].[Fantasy Team]
  4. ORDER BY [Total Of W]/([Total Of W]+[Total Of L])*100 DESC;
  5.  
This will force a divide by 1 when no records found.
Oct 11 '07 #8

P: 15
I still get the overflow error. Can we not have zero in the numerator also?
Oct 11 '07 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. 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%]
  2. FROM [SB Wins_CrossTab2] INNER JOIN [SB Losses_CrossTab2] 
  3. ON [SB Wins_CrossTab2].[Fantasy Team] = [SB Losses_CrossTab2].[Fantasy Team]
  4. ORDER BY [Total Of W]/nz([Total Of W]+[Total Of L],1)*100 DESC;
  5.  
Oct 11 '07 #10

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

I definetly owe you a few beers after this is all done.
Oct 11 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 11 '07 #12

P: 15
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
.....
Oct 12 '07 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. 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%]
  2. FROM [SB Games]
  3. GROUP BY [SB Games].[Fantasy Team]
  4. ORDER BY [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 DESC;
  5.  
Oct 12 '07 #14

P: 15
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.
Oct 12 '07 #15

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this...

Expand|Select|Wrap|Line Numbers
  1. SELECT [SB Games].[Fantasy Team], 
  2. NZ(Count([SB Games].[W/L]="W"),0) AS [Total Of W],
  3. NZ(Count([SB Games].[W/L]="L"),0) AS [Total Of L], 
  4. [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
  5. FROM [SB Games]
  6. GROUP BY [SB Games].[Fantasy Team]
  7. ORDER BY [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 DESC;
  8.  
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.
Oct 12 '07 #16

P: 15
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.
Oct 12 '07 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this...

Expand|Select|Wrap|Line Numbers
  1. SELECT [SB Games].[Fantasy Team], 
  2. Sum(IIf([SB Games].[W/L]="W",1,0)) AS [Total Of W],
  3. Sum(IIf([SB Games].[W/L]="L",1,0)) AS [Total Of L],
  4. [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
  5. FROM [SB Games]
  6. GROUP BY [SB Games].[Fantasy Team]
  7.  
Oct 12 '07 #18

P: 15
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.
Oct 12 '07 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
OK put the code in the query without the Order by. Switch to Access Design view and set win% to descending.

Expand|Select|Wrap|Line Numbers
  1. SELECT [SB Games].[Fantasy Team], 
  2. Sum(IIf([SB Games].[W/L]="W",1,0)) AS [Total Of W],
  3. Sum(IIf([SB Games].[W/L]="L",1,0)) AS [Total Of L],
  4. [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
  5. FROM [SB Games]
  6. GROUP BY [SB Games].[Fantasy Team]
  7.  
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
Oct 13 '07 #20

P: 15
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.
Oct 13 '07 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Oct 13 '07 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
OK, one last try ...

Expand|Select|Wrap|Line Numbers
  1. SELECT [SB Games].[Fantasy Team], 
  2. Sum(IIf([SB Games].[W/L]="W",1,0)) AS [Total Of W],
  3. Sum(IIf([SB Games].[W/L]="L",1,0)) AS [Total Of L],
  4. [Total Of W] / NZ([Total Of W]+[Total Of L],1)*100 As [win%]
  5. FROM [SB Games]
  6. GROUP BY [SB Games].[Fantasy Team]
  7. 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
  8.  
Oct 13 '07 #23

P: 15
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
Oct 14 '07 #24

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 14 '07 #25

P: 15
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."
Oct 14 '07 #26

MMcCarthy
Expert Mod 10K+
P: 14,534
Change

Expand|Select|Wrap|Line Numbers
  1. NZ([Total Of W]+[Total Of L],1)*100
to

Expand|Select|Wrap|Line Numbers
  1. IIf([Total Of W]+[Total Of L]=0,1,[Total Of W]+[Total Of L])*100
and change

Expand|Select|Wrap|Line Numbers
  1. NZ((Sum(IIf([SB Games].[W/L]="W",1,0))+ Sum(IIf([SB Games].[W/L]="L",1,0))),1)*100
to

Expand|Select|Wrap|Line Numbers
  1. 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
Oct 14 '07 #27

P: 15
OK that worked.

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

Thanks again
Oct 15 '07 #28

MMcCarthy
Expert Mod 10K+
P: 14,534
OK that worked.

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

Thanks again
You're welcome.
Oct 15 '07 #29

Post your reply

Sign in to post your reply or Sign up for a free account.