using NZ to count a number of items in a filed and show 0 as well

[itzikd]

i have a table of pilots that have 4 places of ressidence now i have a qry that is suppose to show how many pilots are in each ressidence , i tryed using the simple count funcion but it wont show ressidence if no pilots lives there.
i want the qry to show 0 if no pilots leave in that place

i tryed using Expr1: Nz(Count([BaseResidence],'0') but it won display anything.

this is the sql
SELECT tbl_Pilots.BaseResidence, Count(tbl_Pilots.BaseResidence) AS CountמתוךBaseResidence, Nz([BaseResidence],'0') AS Expr1
FROM tbl_Pilots
GROUP BY tbl_Pilots.BaseResidence, Nz([BaseResidence],'0');

[MikeTheBike]

Hi

I don't think trying to Count() is the corrct function here.
I would use something like this

SELECT tbl_Pilots.BaseResidence, Sum(IIF(IsNull(tbl_Pilots.BaseResidence),0,1)) AS CountOfBaseResidence 
FROM tbl_Pilots
GROUP BY tbl_Pilots.BaseResidence;
 

HTH


MTB

[itzikd]

Hello thanks for your help but this still doesnt work.
here is a picture of the data fields and the qry
as you can see it shows what i put but i have also 2 more fields and it doesnt show 0 with them.

[jforbes]

If you want a record for every Residence, then you should select those records first, then either Join to an Aggregate Query of the Counts, or perform a DCount() as a SubQuery.

This method is pretty simple. Use whatever Table that the ComboBox in the lower left of post #3 is based on to start with as it looks like it has a list of all the Residence records. I'm assuming the Table is tblResidence, then create a Query similar to this, but with your actual field and table names:

SELECT 
  BaseResidence
, DCount("BaseResidence", "tbl_Pilots", "BaseResidence='" & [BaseResidence] & "'") AS CountOfBaseResidence 
FROM tblResidence

[itzikd]

thanks again but that doesnt work. im kinda new to access so maybe im doing something wrong
i used this code you sent, but now it shows me twice the base i have 2 pilots in, and once the base i have 1 pilot in, but it doesn't show the 2 other bases that have no pilots
table name is: tbl_Pilots
inside it i have: BaseResidence
which has 4 option for 4 basses: we can call them x y z u
i want to do a qry that shows
x 2
y 1
z 0
u 0
where xyzu are the basses name and 2 1 0 0 is the number of pilots in them
this is the code from qry, thanks again!

SELECT tbl_Pilots.BaseResidence, DCount("BaseResidence","tbl_Pilots","BaseResidence='" & [BaseResidence] & "'") AS CountOfBaseResidence
FROM tbl_Pilots;

[PhilOfWalton]

Sorry to but in folks, but the database is not normalised and that doesn't help.

There should be a table of Residences and a table of Pilots with a Foreign Key pointing to their Residence.

A very rough guide to normalisation is that Pilots exist in their own right and a table should contain only information about pilots.
(Name, age sex etc)

Residences exist in their own right and have absolutely nothing to do with pilots. I live in a residence. TheResidence table will have information like address, land line number etc.

Now to associate a pilot with a residence you need a pointer in the Pilot table - ResidenceID

If you do that, I think something like this will work:-

SELECT Residence.Residence, Count(Pilot.PilotName) AS CountOfPilotName
FROM Pilot RIGHT JOIN Residence ON Pilot.ResidenceID = Residence.ResidenceID
GROUP BY Residence.Residence;
 

[jforbes]

I assumed there would be a Table named something like tblResidence that was the ControlSource of the ComboBox shown in the Picture. Sounds like that is not the case, since you based the Query on tbl_Pilots.

Like Phil has posted, Normalizing the Residences into their own table would probably be best and his solution should work well if you are up to adding the Detail.

If you still want to create your query based on your un-normalized table, this might work for you. Create a Query called qry_BaseResidence:

SELECT Distinct BaseResidence FROM tbl_Pilots

Then you can write either this Query:

SELECT 
   BaseResidence
 , DCount("BaseResidence", "tbl_Pilots", "BaseResidence='" & [BaseResidence] & "'") AS CountOfBaseResidence 
 FROM qry_BaseResidence

Or this one based on Phil's suggestion:

SELECT qry_BaseResidence.BaseResidence , Count(tbl_Pilots.PilotName) AS CountOfPilotName
FROM tbl_Pilots RIGHT JOIN qry_BaseResidence ON tbl_Pilots.BaseResidence  = qry_BaseResidence.BaseResidence 
GROUP BY qry_BaseResidence.BaseResidence ;

Beware, there may be typos.