Re: Counting yes values only in query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Katt (anonymous_at_discussions.microsoft.com)
Date: 06/21/04 

Date: Mon, 21 Jun 2004 10:17:49 -0700

Sorry to be a pain about this but I get an error message
from this statement. I am prompted to enter a query
restriction. Yes, Y, and True elicit no responses.
Is there a way to do this from design view instead of in
the SQL writing mode? Sorry to be picky but I need a
query to disregard any NO statements.
-Katt
>-----Original Message-----
>Pardon me for jumping in.
>
>REVISED QUERY: This assumes that when you are counting
you want to count "yes"
>values in the field AND that the field is a Yes/No type
(boolean) field. I've
>also aliased the table name as I to make it easier to
type the revision
>
>
>SELECT Format([I].[Date of decision/interview],'mmmm
yyyy')
>AS [Date of decision/interview By Month],
>[I].[Form type],
>Abs(Sum([I].[Initial Interview])) AS [CountOfInitial
Interview],
>Abs(Sum([I].[Initial Interview No Show])) AS
[CountOfInitial Interview No Show],
>Abs(Sum([I].[No Show Rescheduled]) )AS [CountOfNo Show
Rescheduled],
>Abs(Sum([I].Approve)) AS CountOfApprove,
>Abs(Sum([I].[Deny - Other]) )AS [CountOfDeny - Other],
>Abs(Sum([I].[Deny - Fraud])) AS [CountOfDeny - Fraud],
>Abs(Sum([I].[Continued?]) )AS [CountOfContinued?],
>Abs(Sum([Initial entry].[Continued Grant]) )AS
[CountOfContinued Grant],
>Abs(Sum([I].[Continued Deny - Other])) AS
[CountOfContinued Deny - Other],
>Abs(Sum([I].[Continued Deny - Fraud])) AS
[CountOfContinued Deny - Fraud]
>FROM [Initial entry] As I
>GROUP BY Format([I].[Date of decision/interview],'mmmm
yyyy'),
> [I].[Form type],
> Year([I].[Date of decision/interview]) *12+
> DatePart('m',[I].[Date of decision/interview])-1
>ORDER BY Format([I].[Date of decision/interview],'mmmm
yyyy'),
> [I].[Form type];
>.
>

Relevant Pages

  • Re: OutputTo to Excel question
    ... I can't help you with the macro error message as I don't use macros. ... the result from a query. ... Save a query that pulls the same data as the report. ...
    (microsoft.public.access.macros)
  • Re: Background Web Queries flaws!
    ... There is no way to trap errors on a background query. ... There is no way to control the timeout setting. ... The basic problem is that IE spits an error message before returning control ... > hard coded a 5 minute limit into the web query system. ...
    (microsoft.public.excel.programming)
  • Re: Update Query only works first time
    ... I don't know either -- but that is what you error message says -- perhaps you are not using an updateable recordset or records were locked by something else like a form or query ... Microsoft Access MVP 2006 ... It works the first time, but then there is a very long error message with several reasons why it does not work. ...
    (microsoft.public.access.queries)
  • Re: SQL Compact Edition does not recognize parameter
    ... you might try looking in the debugger to see what the parameters collection looks like before you execute the query. ... assured that all computers have the same installation. ... It's always the same error message althought there are 35 parameters ... Only the hardware is different but on one PDA the problem exists ...
    (microsoft.public.sqlserver.ce)
  • Re: Update Query only works first time
    ... your error message indicates that 35 records are locked, which is why they cannot be updated -- do you have another process which has these records tied up? ... Microsoft Access did not update 0 fielddue to a type conversion failure, 0 recorddue to key violations, 35 recorddue to lock violations, and 0 Recorddue to validation rule violations. ... This occurs after I have run the Query the first time. ... It works the first time, but then there is a very long error message with several reasons why it does not work. ...
    (microsoft.public.access.queries)