cansevin
asked on
Count if "Yes"
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!
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!
Select count(CheckPay) from [tablename] where CheckPay = 'Yes'
In the query designer under the CheckPay field in the criteria row type: Yes.
ASKER
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.
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.
What is the datatype of the column?
@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...
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...
try
Select count(CheckPay) from [tablename] where CheckPay = -1
Select count(CheckPay) from [tablename] where CheckPay = -1
@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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@cansevin
did you try my post at http:#a39953502 ?
try placing -1 in your criteria in place of YES
or use <> 0
did you try my post at http:#a39953502 ?
try placing -1 in your criteria in place of YES
or use <> 0
have you tried "true"?
Select count(CheckPay) from [tablename] where CheckPay = true
or, minus one
Select count(CheckPay) from [tablename] where CheckPay = -1
Select count(CheckPay) from [tablename] where CheckPay = true
or, minus one
Select count(CheckPay) from [tablename] where CheckPay = -1