[AccessD] Count query

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.