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

[WolverYanks]

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.

[Delerna]

I think I follow your design ?


How about this
[code=sql]
SELECT a.[Game Date],
Sum( IIF( b.[Home Score] < b.[Adj Away Score] , 1 , 0
) AS [Adj Away Wins]
FROM Dates a
LEFT JOIN [NBA Table] b ON a.[Game Date] = b.[Date of Game]
GROUP BY [Game Date];
[/code]

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

[Delerna]

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.

[WolverYanks]

Thanks a bunch. This info was right on. I never thought to embed the IIF inside the SUM expression.

Thanks again!

[MrDeej]

Even simpler. In the query put "nz([calculated field];0)"

";0" or ";anything" will show if [calculated field] is null