Countif on qry

  • Thread starter Thread starter Serendipity
  • Start date Start date
S

Serendipity

I have a qry that has a calculated field that returns yes or no if certain
criteria is met. I now need a count of the yes's per Property Manager. I get
a total count by using count in the total field. How do I limit the count to
only yes rather than yes and no? I tried putting yes n the criteria field
along with like"yes" but no go.... Help Please!

PS... Some of the Yes / No have no Property Managers name assigned. They
come up blank. I need to have these calculated also.

Field PropertyManager InspectionReceived
Table qry2009Inspections qry2009Inspections
Total group by Count
 
"CountIF" is an Excel function.

If you are using an Access query, use the design view and the selection
criterion under that calculated field and set it to "Yes" (if that's what's
being calculated). You will only get the "Yes" records.

Regards

Jeff Boyce
Microsoft Access MVP


--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Please read my post... you will notice that I am using an Access Query, and I
already tried "your solution" with the results not counting only the "yes"
's. I get the total count of yes & no's but not just one of them. I
referenced countif as I am familiar with that function in excel, but I can't
get those results in Access where they are needed for some reason.

Thanks,
Serendipity...
 
Serendipity said:
I have a qry that has a calculated field that returns yes or no if certain
criteria is met. I now need a count of the yes's per Property Manager. I get
a total count by using count in the total field. How do I limit the count to
only yes rather than yes and no? I tried putting yes n the criteria field
along with like"yes" but no go.... Help Please!

PS... Some of the Yes / No have no Property Managers name assigned. They
come up blank. I need to have these calculated also.

Field PropertyManager InspectionReceived
Table qry2009Inspections qry2009Inspections
Total group by Count
Click to expand...


Sum(IIf(yes/no calculation, 1, 0))
 
Using Like "Yes" tells Access to use EXACTLY "Yes" ... if you don't include
wildcard characters, Like = Equals.

You haven't posted the sql statement your query uses... we might be able to
spot something if you post it.

If you have a table named "qry2009Inspections", the implication is you have
other tables that are year-specific. If so, this is not a well-normalized
design.

Based on your earlier description, I don't believe I had a way to know that
you'd already tried what you think I suggested.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP


--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
I misread as I thought you had a Yes/No field --
Try this --
Field Total_Yes: IIF([InspectionReceived] = "Yes", 1, 0)
Table qry2009Inspections
Total Sum

--
Build a little, test a little.


KARL DEWEY said:
Try this --
Field Total_Yes: Abs(Sum([InspectionReceived]))
Table
Total Expression

--
Build a little, test a little.


Serendipity said:
I have a qry that has a calculated field that returns yes or no if certain
criteria is met. I now need a count of the yes's per Property Manager. I get
a total count by using count in the total field. How do I limit the count to
only yes rather than yes and no? I tried putting yes n the criteria field
along with like"yes" but no go.... Help Please!

PS... Some of the Yes / No have no Property Managers name assigned. They
come up blank. I need to have these calculated also.

Field PropertyManager InspectionReceived
Table qry2009Inspections qry2009Inspections
Total group by Count
Click to expand...
Click to expand...
 
Back
Top