[AccessD] Count query

Oleg_123 at xuppa.com Oleg_123 at xuppa.com
Wed Feb 25 14:01:31 CST 2004


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





More information about the AccessD mailing list