Solved

Count if "Yes"

Posted on 2014-03-25
12
353 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 27

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
Industry Leaders: 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!

 
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 500 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 48

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

685 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