Solved

Count if "Yes"

Posted on 2014-03-25
12
351 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

789 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