adding yes/no option 1

[tatika]

For each record on my table I created a yes/no option that is selected at data entry.

I created a query based on the table values and set the yes/no option to count. So, for each record I generate it counts the yes/no option as 1. In order to add all my counts, I proceded on creating an adding field based on its count but was unsucessful.

My goal is to analyse how many yes and how many no options based on the entire table I have.

Hope I didn't confuse you :-(

Thanks.

 

[lespaul]

SELECT SUM(IIF(fldName = 'Yes', 1, 0)) As YesCount, SUM(IIF(fldName = 'No', 1, 0)) As NoCount From tblName


Fix the table names and field name and it should work.

leslie

 

[tatika]

Were should I insert the code?
In a new field in the query or as a subquery separately?
thanks,

 

[lespaul]

If you open a new query, change the view to SQL view, change the field names and table names and run it!



Leslie

 

[tatika]

Thanks for helping Leslie.
I did insert the information just as you instructed, but when I ran it it gives me only the listing of 1 and 0. It also asks for the criteria before running it.
It is not adding as I would like to...
Thanks for the help anyway

 

[lespaul]

Could you post the SQL you ran? also, your tablename and fieldnames?

Leslie

 

[tatika]

I worked a little bit on it and know what I get is the same total for both options:
sql code:
SELECT Count([tblEvent].[Sum(IIf(tblEvent].[ThirdParty='yes',1,0))]) AS YesCount, Count([tblEvent].[Sum(IIf(tblEvent].[ThirdParty='No',1,0))]) AS NoCount
FROM tblEvent;

query view:
YesCount NoCount
905 905

 

[lespaul]

What does this query return for you?

SELECT Sum(IIf([tblEvent].[ThirdParty] ='Yes',1,0)) AS YesCount, Sum(IIf([tblEvent].[ThirdParty] ='No',1,0)) AS NoCount
FROM tblEvent;


Leslie

 

[tatika]

Gives me a Data type mismatch in criteria expression

 

[lespaul]

remove the ' ' from around the 'Yes' and 'No'

You may need to change them to True or False. What is the field type in the table?

leslie

 

[tatika]

Leslie,
You have no idea how I appreciate all of your help today.
It WORKS. I remove the ''.

Thank you sooooo much

 

[lespaul]

no problem, that's what TekTips is ALL about! Glad you got it working!

leslie