Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Count if "Yes"

I am trying to make a query that counts any check mark that is "Yes". The field name is "CheckPay", if that is checked on... then I want the query to count it.

I am guessing I need to build an expression that says YesField: "If CheckPay is Yes, count it".

Not sure how to write that expression. I am building the query in the design sections. Not the sql. Thanks!
0
cansevin
Asked:
cansevin
  • 3
  • 3
  • 2
  • +3
1 Solution
 
englanddgCommented:
Select count(CheckPay) from [tablename] where CheckPay = 'Yes'
0
 
MacroShadowCommented:
In the query designer under the CheckPay field in the criteria row type: Yes.
0
 
cansevinAuthor Commented:
MarcroShadow... unfortunately that isn't working. That is the type of solution I am looking for. I tried Yes, True, and On. All of them still count all the entries, not the "Yes" ones.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
dustockCommented:
With query designer add the field you want then click on the Totals button.  A 'Total:' section will show up, choose count from that.  Move to the right and select the same field, then select where from the Total section.  In Criteria type 'Yes' with single quotes and run the query.  The below image is what it should look like.

Query Designer
0
 
englanddgCommented:
What is the datatype of the column?
0
 
englanddgCommented:
@dustock

Right, or click on SQL view and use the query I posted earlier.

Same results.

I wonder though, if he's having an issue with the conditional statement due to the datatype.

He's saying the value to filter by is 'Yes', but it may not be (for example, it could be True / False)

This would change the format of the query...

I could be missing something though...
0
 
Rey Obrero (Capricorn1)Commented:
try


Select count(CheckPay) from [tablename] where CheckPay = -1
0
 
dustockCommented:
@englanddg
Yeah I know its the same as your query, just trying to give him direction in the designer view since he mentioned he was using that.

I agree with you on the data type, its adds more confusion when he mentions he tried True and On in addition to yes.  But I'll go with the way the questions was asked and hope he missed the single quotes or something.
0
 
cansevinAuthor Commented:
Thanks guys... sorry for being an idiot on this! Below is a screen shot of what I am doing. The 1st column groups the data by "WhoBooked"
The 2nd column counts the total booked by counting the "BookNumber"
The 3rd column should count only if the "CheckPay" is checked. Or "Yes". It is a "Yes/No" field type on the table. This 3rd column is not counting correctly.

Doc2.pdf
0
 
dustockCommented:
Have you tried putting Yes in single quotes?
0
 
Rey Obrero (Capricorn1)Commented:
@cansevin

did you try my post at http:#a39953502 ?


try placing  -1 in your criteria in place of YES

or use   <> 0
0
 
PaulCommented:
have you tried "true"?

Select count(CheckPay) from [tablename] where CheckPay = true

or, minus one

Select count(CheckPay) from [tablename] where CheckPay = -1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now