Counting YES NO Values 1

[Brogrim]

I have a table with a number of Yes No fields, I want to be able to run a query that will count a value that is marked as true in these YES NO fields.

 

[HarleyQuinn]

At the end of your queries SQL addthe following line:

HAVING YOURYESNOFIELD = True

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[Brogrim]

Thanks for that, unfortunately I am buildung this query in the Access, would there be an example by an chance,

 

[alvechurchdata]

Access stores "Yes" as -1 so you could just sum the YesNo field in the query and multiply the answer by -1.

Another method is to create a new column in the query and use the Iif() function to conver the YesNo to numeric:

Iif(YesNoField, 1, 0)

And then sum this field.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[HarleyQuinn]

In the query add your identifier field (e.g. NAMES). Then add the YES/NO column. Click the TOTALS button and set the YES/NO column's Total: value to count. Add the YES/NO column again and change the totals value to WHERE. Set the criteria to True. That should give you a count of the yes's.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[Brogrim]

Thanks for the help, I think I am getting there. I have a table of members who all do different activities.

Name Text
Soccer Yes / No
Skiing Yes / No
Basketball Yes / No

I want to run a query that will give me the number of members who partake in each sport.

I hav etried the above methods but to no avail

 

[HarleyQuinn]

What didn't work for you with the above examples?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

Try this.

Open a new query, add the following fields from your table:

Name - Once
Text - Twice

Click the Totals button.
Change the totals for the fields to be:

Name - Group By
Text - Count
Text - Where

In the criteria for the second Text column add the Criteria True.

Run your query.

That will give you (by each sport) a number of Yes's.

Hope this helps



Harleyquinn

---------------------------------
For tsunami relief donations

 

[pbrodsky]

Actually, that type of thing is frequently done with an iif and a sum.

eg. something like
SELECT tbl1.name, Sum(IIf([q],1,0)) AS nq
FROM tbl1
GROUP BY tbl1.name;

 

[lespaul]

A more long term solution would be to normalize your data. Assuming there is a person who has answered yes/no to each of these activities, what you have is a many to many relationship (one person can have many activities each activity can have many people who do it). Your structure should be:

People
ID
Name
Other personal Info

Activities
ID
Description

PeopleActivities
PeopleID
ActivityID

With your current structure, anytime you need to add a new activities (Basketweaving, Horseback Riding, etc.) you are going to have to change the table structure to include the new activity AND change all the queries to include the new field. With the normalized structure, you would just have to add a new record to the Activity table.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Already covered so many times ...
SELECT Sum(Abs([Soccer])) AS CountOfSoccer, Sum(Abs([Skiing])) AS CountOfSkiing, Sum(Abs([Basketball])) AS CountOfBasketball
FROM yourTable;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Brogrim]

Thanx alot everybody, working perfect.

 

[dhookom]

I doubt it is "working perfect" unless you have normalized your tables as suggested by Leslie (who needs a star).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]