Solved

Count if "Yes"

Posted on 2014-03-25
12
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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

[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

617 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