470,594 Members | 1,131 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

Access 2003, how do I show "zero" values in a query, where the field is calculated?

SELECT Dates.[Game Date], Sum(1) AS [Adj Away Wins]
FROM Dates LEFT JOIN [NBA Table] ON Dates.[Game Date] = [NBA Table].[Date of Game]
WHERE ((([NBA Table].[Home Score])<[Adj Away Score]))
GROUP BY Dates.[Game Date];

I'm doing a sports database, for entertainment and to learn Access, so hopefully this is an easy fix. I have a Dates table, with each date of the year. I have another table with raw game data for each basketball game.

Let's say one day there are 5 games. Let's also assume that all 5 of these games are won by the "Home Team". Because "Away Team" has 0 wins on this date, when I SUM the total for this given date, it's 0 and will not show up in my query. I want the 0 to be present, so that later on I can run mathematical operations on this number.

I was able to use an IIf statement on other queries, where no calculations were done, but can't figure this one out.

Thanks in advance and let me know if I need to post more.
Dec 21 '09 #1
4 10983
1,134 Expert 1GB
I think I follow your design ?

How about this
Expand|Select|Wrap|Line Numbers
  1. SELECT a.[Game Date], 
  2.        Sum(  IIF( b.[Home Score] < b.[Adj Away Score] , 1 , 0 
  3.            ) AS [Adj Away Wins]
  4. FROM Dates a
  5. LEFT JOIN [NBA Table] b ON a.[Game Date] = b.[Date of Game]
  6. GROUP BY [Game Date];
There would be otherways too.
Some will run faster than others.
Maybe you should try to find a few other ways and see which performs best
Dec 22 '09 #2
1,134 Expert 1GB
Oh, and the real reason that the dates that had no away wins were missing was because of this

WHERE ((([NBA Table].[Home Score])<[Adj Away Score]))

which filters every record, where the away team lost, out of the result, prior to doing the aggregation.
So if no away team won on a particular date then every record for that date
gets filtered out of the result.

That filtering occurred before the sum() so its really not because the sum equalled 0.
I know what you mean though.
Dec 22 '09 #3
Thanks a bunch. This info was right on. I never thought to embed the IIF inside the SUM expression.

Thanks again!
Dec 22 '09 #4
157 100+
Even simpler. In the query put "nz([calculated field];0)"

";0" or ";anything" will show if [calculated field] is null
Dec 22 '09 #5

Post your reply

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

Similar topics

2 posts views Thread by Angus | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.