Counting yes values only in query

[Katt]

When I put yes - there is no change. It seems to be
counting the number of entries and not the number of
yes/no answers.
How do I get the query to pay attention to yes/no (count
number of yes) instead of "count total number of records".
-Katt

 

[Katrina]

Use =True (Rather than =Yes) as your criteria

HTH
Kat

 

[Guest]

Still counting number of entries and not paying attention
to number of yes/true values.
-HELP!
-Katt

 

[Katrina]

What is the SQL statement that it is using?

Kat

Still counting number of entries and not paying attention
to number of yes/true values.
-HELP!
-Katt

 

[Duane Hookom]

Please keep all posts in the original thread. There is a suggested technique
there that should work. If not, then post a reply within that thread.

 

[Guest]

Sorry - I am not totally familar with SQL (at least the
terminology). I built the query with the wizard and did
summary - checked "count all entries" - and now I am
working in design view to try and get the Criteria correct.
In design view I have a row called TOTAL:
where count appears.
-Katt

 

[Katrina]

Click on view then, SQL view and you will see the code
copy and paste it.
to get back to design view...ckick on view again then design view.

Kat

 

[Guest]

When I put the COUNTYES... in the Criteria row I get an
error message about using a dot or !
-Katt (sorry about reposting-having issues staying online)

 

[KAtt]

SQL of Query:
SELECT DISTINCTROW Format$([Initial entry].[Date of
decision/interview],'mmmm yyyy') AS [Date of
decision/interview By Month], [Initial entry].[Form type],
Count([Initial entry].[Initial Interview]) AS
[CountOfInitial Interview], Count([Initial entry].[Initial
Interview No Show]) AS [CountOfInitial Interview No Show],
Count([Initial entry].[No Show Rescheduled]) AS [CountOfNo
Show Rescheduled], Count([Initial entry].Approve) AS
CountOfApprove, Count([Initial entry].[Deny - Other]) AS
[CountOfDeny - Other], Count([Initial entry].[Deny -
Fraud]) AS [CountOfDeny - Fraud], Count([Initial entry].
[Continued?]) AS [CountOfContinued?], Count([Initial
entry].[Continued Grant]) AS [CountOfContinued Grant],
Count([Initial entry].[Continued Deny - Other]) AS
[CountOfContinued Deny - Other], Count([Initial entry].
[Continued Deny - Fraud]) AS [CountOfContinued Deny -
Fraud]
FROM [Initial entry]
GROUP BY Format$([Initial entry].[Date of
decision/interview],'mmmm yyyy'), [Initial entry].[Form
type], Year([Initial entry].[Date of decision/interview])
*12+DatePart('m',[Initial entry].[Date of
decision/interview])-1
ORDER BY Format$([Initial entry].[Date of
decision/interview],'mmmm yyyy'), [Initial entry].[Form
type];
-Katt

 

[Duane Hookom]

I didn't suggest that you put the expression in the criteria. I assumed you
had a totals query and the expression that I gave you would be in the Field
row, not the criteria row. Counting is always done in a totals/group by
query.

 

[Katt]

Sorry-since that was an area to modify the query in design
view I assumed you ment place it there.
When I built the query I used the wizard and instead of
picking details I picked summary then count records.
Now I am trying to get the query to understand that I only
want the count of the "yes" items. The table reads (in
design view) yes/no value with default of no. They are
check boxes on the form and appear as check boxes in the
table.
-Katt (just an access lover not a major player)

 

[John Spencer (MVP)]

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(.[Date of decision/interview],'mmmm yyyy')
AS [Date of decision/interview By Month],
.[Form type],
Abs(Sum(.[Initial Interview])) AS [CountOfInitial Interview],
Abs(Sum(.[Initial Interview No Show])) AS [CountOfInitial Interview No Show],
Abs(Sum(.[No Show Rescheduled]) )AS [CountOfNo Show Rescheduled],
Abs(Sum(.Approve)) AS CountOfApprove,
Abs(Sum(.[Deny - Other]) )AS [CountOfDeny - Other],
Abs(Sum(.[Deny - Fraud])) AS [CountOfDeny - Fraud],
Abs(Sum(.[Continued?]) )AS [CountOfContinued?],
Abs(Sum([Initial entry].[Continued Grant]) )AS [CountOfContinued Grant],
Abs(Sum(.[Continued Deny - Other])) AS [CountOfContinued Deny - Other],
Abs(Sum(.[Continued Deny - Fraud])) AS [CountOfContinued Deny - Fraud]
FROM [Initial entry] As I
GROUP BY Format(.[Date of decision/interview],'mmmm yyyy'),
.[Form type],
Year(.[Date of decision/interview]) *12+
DatePart('m',.[Date of decision/interview])-1
ORDER BY Format(.[Date of decision/interview],'mmmm yyyy'),
.[Form type];

 

[Duane Hookom]

John Spencer has applied my solution correctly.

 

[Katt]

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(.[Date of decision/interview],'mmmm yyyy')
AS [Date of decision/interview By Month],
.[Form type],
Abs(Sum(.[Initial Interview])) AS [CountOfInitial Interview],
Abs(Sum(.[Initial Interview No Show])) AS

[CountOfInitial Interview No Show],

Abs(Sum(.[No Show Rescheduled]) )AS [CountOfNo Show Rescheduled],
Abs(Sum(.Approve)) AS CountOfApprove,
Abs(Sum(.[Deny - Other]) )AS [CountOfDeny - Other],
Abs(Sum(.[Deny - Fraud])) AS [CountOfDeny - Fraud],
Abs(Sum(.[Continued?]) )AS [CountOfContinued?],
Abs(Sum([Initial entry].[Continued Grant]) )AS [CountOfContinued Grant],
Abs(Sum(.[Continued Deny - Other])) AS

[CountOfContinued Deny - Other],

Abs(Sum(.[Continued Deny - Fraud])) AS

[CountOfContinued Deny - Fraud]

FROM [Initial entry] As I
GROUP BY Format(.[Date of decision/interview],'mmmm yyyy'),
.[Form type],
Year(.[Date of decision/interview]) *12+
DatePart('m',.[Date of decision/interview])-1
ORDER BY Format(.[Date of decision/interview],'mmmm yyyy'),
.[Form type];
.

 

[John Spencer (MVP)]

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

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(.[Date of decision/interview],'mmmm yyyy')
AS [Date of decision/interview By Month],
.[Form type],
Abs(Sum(.[Initial Interview])) AS [CountOfInitial Interview],
Abs(Sum(.[Initial Interview No Show])) AS

[CountOfInitial Interview No Show],

Abs(Sum(.[No Show Rescheduled]) )AS [CountOfNo Show Rescheduled],
Abs(Sum(.Approve)) AS CountOfApprove,
Abs(Sum(.[Deny - Other]) )AS [CountOfDeny - Other],
Abs(Sum(.[Deny - Fraud])) AS [CountOfDeny - Fraud],
Abs(Sum(.[Continued?]) )AS [CountOfContinued?],
Abs(Sum([Initial entry].[Continued Grant]) )AS [CountOfContinued Grant],
Abs(Sum(.[Continued Deny - Other])) AS

[CountOfContinued Deny - Other],

Abs(Sum(.[Continued Deny - Fraud])) AS

[CountOfContinued Deny - Fraud]

FROM [Initial entry] As I
GROUP BY Format(.[Date of decision/interview],'mmmm yyyy'),
.[Form type],
Year(.[Date of decision/interview]) *12+
DatePart('m',.[Date of decision/interview])-1
ORDER BY Format(.[Date of decision/interview],'mmmm yyyy'),
.[Form type];
.