Nz function not working

[Beerman]

I am trying to run a query based off of a single table that lists global shipments. Basicaly what I am trying to do is count the number of UPS shipments (Carrier) from each country, the problem is not all countries are sending via UPS and therefore it doesn't include that country on the results. Then once I run the month end Report, it excludes the country because it had a Null value in this query.

SELECT[GPMO].[From Country], Count([GPMO].Carrier) AS CountofCarrier
FROM[GPMO]
WHERE(((GPMO].Carrier)="UPS"))
GROUP BY[GPMO].[From Country]


ORDER BY Count([GPMO].Carrier) DESC;

I have tried:
SELECT[GPMO].[From Country], Nz(Count([GPMO].Carrier),0)
However this has not worked. Any suggestions?

[JoeM]

One simple way is to create another aggregate query that just returns all your different countries.
Then do a Left Join from this new query to the query you posted above, and it should show all the countries in it, and the total Carrier Count (will need to use the NZ function to return 0 in the place of Null for those without counts).

[rpeare]

SELECT[GPMO].[From Country], Sum(iif([GPMO].Carrier = "UPS", 1, 0)) AS CountofCarrier
FROM[GPMO]
GROUP BY[GPMO].[From Country]
ORDER BY Count([GPMO].Carrier) DESC;

[JoeM]

rpeare,

That's a clever way. I should have remembered that, I have used that method before in the past!

[rpeare]

like a river I seek the path of least resistance