Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Count if "Yes"

Posted on 2014-03-25
12
Medium Priority
?
365 Views
Last Modified: 2014-03-26
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
Comment
Question by:cansevin
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 3

Expert Comment

by:englanddg
ID: 39953381
Select count(CheckPay) from [tablename] where CheckPay = 'Yes'
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39953391
In the query designer under the CheckPay field in the criteria row type: Yes.
0
 

Author Comment

by:cansevin
ID: 39953424
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Expert Comment

by:dustock
ID: 39953484
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
 
LVL 3

Expert Comment

by:englanddg
ID: 39953492
What is the datatype of the column?
0
 
LVL 3

Expert Comment

by:englanddg
ID: 39953500
@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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39953502
try


Select count(CheckPay) from [tablename] where CheckPay = -1
0
 
LVL 9

Expert Comment

by:dustock
ID: 39953574
@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
 

Author Comment

by:cansevin
ID: 39953608
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
 
LVL 9

Accepted Solution

by:
dustock earned 2000 total points
ID: 39953639
Have you tried putting Yes in single quotes?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39953661
@cansevin

did you try my post at http:#a39953502 ?


try placing  -1 in your criteria in place of YES

or use   <> 0
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39955328
have you tried "true"?

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

or, minus one

Select count(CheckPay) from [tablename] where CheckPay = -1
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question