[AccessD] Count query

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);




> Hi All,
>
> I think Gustav was on the right track with the SUM.  If you include and
> IIF statement and attribute 1's and 0's depending on whether they meet
> the  criteria, you could then put a count on each respective column to
> get the  totals you want.
>
> Something along the lines of...(not sure if the syntax is correct here,
> have just added it to give you an idea)
>
> SELECT Sum(IIF(Not IsNull(FileToSupervisor),1,0)) AS CheckedID,
> Sum(IIF(IsNull(FileToSupervisor),1,0)) AS NotCheckedID
> FROM [Ron Data mid AP]
> WHERE (((Date()-[MS Date Opened])<149))
>
> This will give you the totals for the checked and unchecked in one
> query,  if that is what you want.
>
> Cheers
> Ryan
>
>
>
>
>
>
> <Oleg_123 at xuppa.com>
> Sent by: accessd-bounces at databaseadvisors.com
> 25/02/2004 16:05
> Please respond to Access Developers discussion and problem solving
>
>
>         To:     <accessd at databaseadvisors.com>
>         cc:
>         Subject:        Re: [AccessD] Count query
>
>
> Hi Gustav,
>
> i need a count, not a sum :--)
> when i am trying count this way -- i just get a same number in both
> columns.
> I don't think Union Query would work here
>
> SELECT Count((Not IsNull(FileToSupervisor))) AS CheckedID,
> Count((IsNull(FileToSupervisor))) AS NotCheckedID
> FROM [Ron Data mid AP]
> WHERE (((Date()-[MS Date Opened])<149));
>
>
>
>> Hi Oleg
>>
>> How about this:
>>
>> 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);
>>
>> /gustav
>>
>>
>>> Is it possible for one query to count how many records are up to 150
>>> days old and have been chechked and the ones that haven't ? I
>>> understand how to do in 2 queris, i am wondering if its possible to
>>> do in one
>>
>>> thanks,
>>> Oleg
>>
>>> SELECT Count([Ron Data mid AP].ID) AS CountOfID
>>> FROM [Ron Data mid AP]
>>> WHERE (((Date()-[Ron Data mid AP].[MS Date Opened])<149))AND (([Ron
>>> Data mid AP].FileToSupervisor) Is not Null));
>>
>>> SELECT Count([Ron Data mid AP].ID) AS CountOfID
>>> FROM [Ron Data mid AP]
>>> WHERE (((Date()-[Ron Data mid AP].[MS Date Opened])<149))AND (([Ron
>>> Data mid AP].FileToSupervisor) Is Null));
>>
>> --
>> _______________________________________________
>> 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
>
>
>
>
>
>
> This e-mail and any attachment are confidential and contain proprietary
> information, some or all of which may be legally privileged.  It is
> intended solely for the use of the individual or entity to which it is
> addressed.  If you are not the intended recipient, please notify the
> author immediately by telephone or by replying to this e-mail, and then
> delete all copies of the e-mail on your system.  If you are not the
> intended recipient, you must not use, disclose, distribute, copy, print
> or rely on this e-mail.
>
> Whilst we have taken reasonable precautions to ensure that this e-mail
> and any attachment has been checked for viruses, we cannot guarantee
> that they are virus free and we cannot accept liability for any damage
> sustained as a result of software viruses.  We would advise that you
> carry out your own virus checks, especially before opening an
> attachment.
>
> The UK firm Ernst & Young LLP is a limited liability partnership
> registered in England and Wales with registered number OC300001 and is a
> member practice of Ernst & Young Global.  A list of members? names is
> available for inspection at 1 More London Place, London, SE1 2AF, the
> firm?s principal place of business and its registered office. --
> _______________________________________________
> 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