Re: Counting yes values only in query
From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 06/21/04
- Next message: John Spencer (MVP): "Re: Invalid Operation error?"
- Previous message: Ted Allen: "Expression - calculating running total"
- In reply to: Katt: "Re: Counting yes values only in query"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 21 Jun 2004 19:16:25 -0400
First of all WHICH FIELD is yes or no? Is that field a text field, a yes/no
field, or a date field, or a number field?
To trouble shoot this, break the query down to just one YES/No field and see if
you get what you are expecting. Once you have done that then you can expand
your query and add in more fields.
Open a query
Select Totals from the View menu
Enter the following in two field cells.
Field: DateByMonth: FORMAT([Date of Decision/Interview],"mmmm yyyy")
Total: Group By
Field: CountOfInitialInterview: Abs(Sum([Initial Interview]))
Total: Expression
Does that return what you want? If not, what does it return? Does it return an
error or some other message? What is the message? We cannot see what you are
getting, you can. So please help us help you, but telling us what you do see.
Also, don't forget to tell us what type of field [Initial Interview] is? If it
is not a yes/no field, try
Abs(Sum([initial interview]="Yes"))
Katt wrote:
>
> 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];
> >.
> >
- Next message: John Spencer (MVP): "Re: Invalid Operation error?"
- Previous message: Ted Allen: "Expression - calculating running total"
- In reply to: Katt: "Re: Counting yes values only in query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
