[AccessD] Count Problem

Hi Chester

Count() counts records. If no records, no Count(), thus Count = 0 is only possible for counting records of a subquery or with a Left Join to a child table:

SELECT 
  tblParent.ID, 
  Count(tblChild.ID) AS CountOfChilds
FROM 
  tblParent 
  LEFT JOIN 
    tblChild ON 
      tblParent.ID = tblChild.FK
GROUP BY 
  tblParent.ID;

Note that the common method, Count(*), wouldn't work here as that will count rows of the _parent_ table.

Here is how to do. 
Note the use of INT() to provide a numeric output; if omitted, a string will be returned:

SELECT 
  tblParent.ID, 
  INT(IIf(Count([tblChild]![ID])=0,Null,Count([tblChild]![ID]))) AS Childs
FROM 
  tblParent 
  LEFT JOIN 
    tblChild ON 
      tblParent.ID = tblChild.FK
GROUP BY 
  tblParent.ID;

/gustav


>>> Chester_Kaup at kindermorgan.com 08-12-2008 22:44 >>>
What I am trying to do is do a count on the field status for each value it can contain for all the records in a table. If a count of the field returns zero I want to see NULL. If it returns any other value I want to see the actual value. Hope that makes sense. Here is whole SQL string

SELECT IIf([Stat_Code]="RP","RL",IIf([Stat_Code]="SP","SL",
[Stat_Code])) AS Status, [tbl Status Codes Active].Stat_Desc, DateSerial(Year(Date()),Month(Date()),1)-1 AS StatusDate1, IIf(Count([Status])=0,Null,Count([Status])) AS StatusCount, 
[tbl Status Codes Active].Area, [tbl Status Codes Active].UI, 
[tbl Status Codes Active].SortOrderSummary
FROM [qry Well Status 1 Month Prior with Area] 
RIGHT JOIN [tbl Status Codes Active] ON ([qry Well Status 1 Month Prior with Area].Status = [tbl Status Codes Active].Stat_Code) 
AND ([qry Well Status 1 Month Prior with Area].Area = [tbl Status Codes Active].Area)
GROUP BY IIf([Stat_Code]="RP","RL",IIf([Stat_Code]="SP","SL",[Stat_Code])), [tbl Status Codes Active].Stat_Desc, DateSerial(Year(Date()),Month(Date()),1)-1, [tbl Status Codes Active].Area, [tbl Status Codes Active].UI, [tbl Status Codes Active].SortOrderSummary;