Solved

Count if "Yes"

Posted on 2014-03-25
12
347 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 26

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
 
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now