[AccessD] Count query

well you could try 

 SELECT
  Sum(IIf((IsNull([Preliminary Investigation Closed]) AND IsNull([Pre Inv
Disposition])),1,0)) AS OpenPRE_Inv,
 
 Sum(IIf ((Not IsNull([Preliminary Investigation Closed]) and (Not
IsNull([Pre Inv Disposition])) ,1,0)) AS Open_Inv,

 Sum(IIf((Not IsNull([First Investigation Document Review]) and
(IsNull([Depos Complete])),1,0)) AS FirstDocComplete
> FROM [Ron Data sprT];

or switch the 1,0 to 0,1 for the open_inv and leave the isnull same as first
 
SELECT
  Sum(IIf((IsNull([Preliminary Investigation Closed]) AND IsNull([Pre Inv
Disposition])),1,0)) AS OpenPRE_Inv,
 
 Sum(IIf ((IsNull([Preliminary Investigation Closed]) and (IsNull([Pre Inv
Disposition])) ,0,1)) AS Open_Inv,

 Sum(IIf((Not IsNull([First Investigation Document Review]) and
(IsNull([Depos Complete])),1,0)) AS FirstDocComplete
 FROM [Ron Data sprT];

HTH
Patti

> -----Original Message-----
> From: Oleg_123 at xuppa.com [mailto:Oleg_123 at xuppa.com]
> Sent: Wednesday, February 25, 2004 03:02 PM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Count query
> 
> 
> Hello again,
> what about if more then one condition is required ? Like --
> 
> SELECT
>   Sum(IIf(Not IsNull([Preliminary Investigation 
> Closed]),1,0))as OpenPRE_Inv,
>   Sum(IIf(IsNull([Preliminary Investigation Closed]),1,0)) AS 
> Open_Inv,
>   Sum(IIf(Not IsNull([First Investigation Document 
> Review]),1,0))AS       
>  FirstDocComplete
> FROM [Ron Data sprT];
> 
> and the condition for the OpenPRE_Inc column should be ([Preliminary
> Investigation Closed])not Null and another column [Pre Inv 
> Disposition]not
> Null. Same (or actully the opposite) for Open_Inv
> 
> is there a way to combine it ? I tried with "and", didn't 
> seenm to work
> 
> SELECT
> Sum(IIf(IsNull([Preliminary Investigation Closed]),1,0)) AS 
> OpenPRE_Inv,
> --(IsNull([Pre Inv Disposition]))
> Sum(IIf(Not IsNull([Preliminary Investigation Closed]),1,0)) 
> AS Open_Inv,
> --(Not IsNull([Pre Inv Disposition]))
> Sum(IIf(Not IsNull([First Investigation Document Review]),1,0)) AS
> FirstDocComplete
> --( IsNull([Depos Complete]))
> FROM [Ron Data sprT];
> 
> 
> > Hi Oleg
> >
> > Ahh, got it now, omit the ID:
> >
> > SELECT
> >   Sum(Abs(Not IsNull(FileToSupervisor))) AS CheckedID,
> >   Sum(Abs(IsNull(FileToSupervisor))) AS NotCheckedID
> > FROM
> >   [Ron Data mid AP]
> > WHERE
> >   (Date() - [MS Date Opened] < 149);
> >
> > /gustav
> >
> >
> >> hey Ryan, yeah this one calcualates correctly -- 4 done, 480 to go,
> >> the one below gave 321017 and 25515793...
> >> (they wish they had that many cases) :--)
> >
> >> SELECT
> >>   Sum(ID * Abs(Not IsNull(FileToSupervisor))) AS CheckedID,
> >>   Sum(ID * Abs(IsNull(FileToSupervisor))) AS NotCheckedID
> >> FROM
> >>   [Ron Data mid AP]
> >> WHERE
> >>   (Date() - [MS Date Opened] < 149);
> >
> > --
> > _______________________________________________
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> 
> 
> -----------------------------------------
> Get Breaking News from CNN, ABC, NBC, CBS Now.
> http://www.xuppa.com/news/?link=webmail
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>