hi all

how do i use 'count' to return instances where the condition does not occur? (count is zero)???

for example, here's the table (TAB1):

Player Home Away
Jim 10 27
Jim 12 13
Ron 10 7
Ron 52 10
Ron 3 17
Dave 17 10
Dave 0 7

for each distinct Player, i want to know how many records have Home>Away, *even if that number is zero*

so in this example, i want to get back:

Player Wins
Jim 0
Ron 2
Dave 1

i tried this:
select distinct Player, IIF(count(*) is null, 0, count(*))
from TAB1
where Home > Away
group by Player
order by Player

but it still doesnt count Jim's zero wins.

thx!
rudi

Is this for a report?

Doesn't matter. This will show wins & losses. You can delete the losses column if you wish.

SELECT TAB1.Name, Sum(IIf([Home]>[away],1,"0")) AS Wins, Sum(IIf([Home]<[away],1,"0")) AS Losses
FROM TAB1
GROUP BY TAB1.Name
ORDER BY Sum(IIf([Home]>[away],1,"0"))
WITH OWNERACCESS OPTION;

Regards,

Beth M

Sorry, change TAB1.Name to TAB1.Player in all instances. It should read:

SELECT TAB1.Player, Sum(IIf([Home]>[away],1,"0")) AS Wins, Sum(IIf([Home]<[away],1,"0")) AS Losses
FROM TAB1
GROUP BY TAB1.Player
ORDER BY Sum(IIf([Home]>[away],1,"0"))
WITH OWNERACCESS OPTION;

Beth M