Everyone seems to be over complicating it.
In maths if you subtract a negative - you are adding that number as if the negative sign was not there.
For example:
If you have 10 entries, with a Yes/No field. Where 3 are showing Yes...
The sum of those entries is -3.
Call this field fldYesNo, in tblExample
Input a column in the Query with a sum of fldYesNo. Hide this column.
Next to that column have:
Expr1: Sum([tblExample]![fldYesNo]-([tblExample]![fldYesNo]*2))
Which translates to:
Expr1: Sum(-3-(-3*2))
= -3 - (-6)
= -3 + 6
= 3
Hope this helps.
vanderghast wrote:
SELECT COUNT(*)+SUM(question1) AS [q1 was answered no], -SUM(question2) AS
07-Oct-09
SELECT COUNT(*)+SUM(question1) AS [q1 was answered no],
-SUM(question2) AS [q2 was answered yes],
COUNT(*)+SUM(question3) AS [q2 was answered no],
COUNT(*)+SUM(question4) AS [q4 was answered no],
COUNT(*)+SUM(question5) AS [q5 was answered no]
FROM tableName
WHERE theDate BETWEEN now() AND now() - 30
Vanderghast, Access MVP
Previous Posts In This Thread:
Counting Yes/No responses from multiple records in a table
I have a table & form created for logging results of workorder paperwork
audits. There are 5 yes/no questions, like "Is Document 1 missing?", "Is
Document 2 signed?", etc. I have a query & report showing the results for
each record (each workorder audited is a new record in the table). I would
like to create a query that shows a summary of each question for all records
combined, "How many times was Document 1 missing?" ...Document 2 not signed,
etc. Any suggestions are greatly appreciated. Thanks!
ChrisIf I am understanding your description, you have ... a spreadsheet! If
Chris
If I am understanding your description, you have ... a spreadsheet! If each
step in your "logging workorder paperwork" gets it is own field in your
table, this means that every time your logging process changes, you have to
change your table, your form(s), your query(s), your report(s), your ...
That's a maintenance nightmare!
It sounds like you are describing that there can be zero, one, or many steps
in the process that have been completed. That's best represented in a
relational database (hint: Access) by using THREE tables, one for the item
(?workorder) and a second to show the steps completed for the item. The
third table lists the steps.
In this design, if the number (?!or sequence?!) of the steps changes, make
the change in the third table. Everything else just keeps working! No
maintenance. No nightmares.
Good luck!
Regards
Jeff Boyce
Microsoft Access MVP
The way to count yes/no fields that are checked is to use an expression
The way to count yes/no fields that are checked is to use an expression
Abs(Sum([QuestionField])) as CountQField
in a totals (aggregate) query.
You can use that expression in a totals query or you can use it in the
report's footer as the source for a control. You will need an equals sign in
front of the expression if you are doing this in the report's footer.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Chris S. wrote:
Create a total query and SUM the yes/no field: yes is -1, and no=0, so a SUM
Create a total query and SUM the yes/no field: yes is -1, and no=0, so a
SUM returning -6 means you have 6 yes. COUNT(*) would return the total
number of records, so, if the SUM(fieldName) = -6 and COUNT(*) = 10,
then you have 10 - 6, COUNT(*) + SUM(fieldName) times the value NO
(vertically, for the group(s) you made; if you make no group, then the whole
table is one large group).
Vanderghast, Access MVP
JeffSorry for not being more specific, I do in fact have an access database
Jeff
Sorry for not being more specific, I do in fact have an access database for
this. The 5 yes/no questions are identical for each record (my bosses are
only concened with these particular 5 items). I created a simple form with
yes/no checkboxes for each of the 5 areas of concern. The bosses want to know
how many times the answer to each question was "no". I have a table, query,
and form that shows:
Workorder number
question 1: yes/no (whichever was selected-checkbox control)
question 2: yes/no (whichever was selected-checkbox control)
question 3: yes/no (whichever was selected-checkbox control)
question 4: yes/no (whichever was selected-checkbox control)
question 5: yes/no (whichever was selected-checkbox control)
I am now trying to create a query that will allow my bosses to see the
following in a report:
Of all the workorders audited in the past 30 day:
The answer to question 1 was "no" 14 times
The answer to question 2 was "yes" 5 times
The answer to question 3 was "no" 19 times
The answer to question 4 was "no" 3 times
The answer to question 5 was "no" 0 times
Each workorder audit is a separate record in a single table that consists of
the following fields:
workorder number
dateaudited
question1
question2
question3
question4
question5
I have a query that shows the answers for each workorder (record)
individually, but I need one that shows a summary as described above.
Thanks
Jeff Boyce wrote:
SELECT COUNT(*)+SUM(question1) AS [q1 was answered no], -SUM(question2) AS
SELECT COUNT(*)+SUM(question1) AS [q1 was answered no],
-SUM(question2) AS [q2 was answered yes],
COUNT(*)+SUM(question3) AS [q2 was answered no],
COUNT(*)+SUM(question4) AS [q4 was answered no],
COUNT(*)+SUM(question5) AS [q5 was answered no]
FROM tableName
WHERE theDate BETWEEN now() AND now() - 30
Vanderghast, Access MVP
A simple solution for everyone...
Everyone seems to be over complicating it.
In maths if you subtract a negative - you are adding that number as if the negative sign was not there.
For example:
If you have 10 entries, with a Yes/No field. Where 3 are showing Yes...
The sum of those entries is -3.
Call this field fldYesNo, in tblExample
Input a column in the Query with a sum of fldYesNo. Hide this column.
Next to that column have:
Expr1: Sum([tblExample]![fldYesNo]-([tblExample]![fldYesNo]*2))
Which translates to:
Expr1: Sum(-3-(-3*2))
= -3 - (-6)
= -3 + 6
= 3
Hope this helps.
a simple solution...
Everyone seems to be over complicating it.
In maths if you subtract a negative - you are adding that number as if the negative sign was not there.
For example:
If you have 10 entries, with a Yes/No field. Where 3 are showing Yes...
The sum of those entries is -3.
Call this field fldYesNo, in tblExample
Input a column in the Query with a sum of fldYesNo. Hide this column.
Next to that column have:
Expr1: Sum([tblExample]![fldYesNo]-([tblExample]![fldYesNo]*2))
Which translates to:
Expr1: Sum(-3-(-3*2))
= -3 - (-6)
= -3 + 6
= 3
Hope this helps.
Submitted via EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: C# In Depth by Jon Skeet (Manning)
http://www.eggheadcafe.com/tutorial...b020-2b02f9f436a9/book-review-c-in-depth.aspx