Counting YES/NO in a Table with a Query

[JayhawkJim]

I have looked at, read, and tried everything on the Access Discussion Board
back to 2007 but I still can't get this query to return the correct info. A
completed FORM allows users to 'check' a Data Control box to answer a
question with 5 possible degrees of satisfaction. It's the typical control
that enters a YES(-1) for TRUE and a NO(0) for FALSE in the Table. I have
entered 10 test responses into the table via the form successfully. In the
table, I can see that the cumulative sum of the colums are 6,2,2,1,0 for a
total of 11 records (5 fields) - which tells me that the data was entered
correctly. I want the query to display these totals (6,2,2,1,0) so I can
construct a chart of the results. Any help? In the Criteria line, I've
entered, Count(Abs([FieldName])) and Sum(Abs([FieldName])) without success.

 

[Jeff Boyce]

"...without success... " doesn't give us many clues to go on. What happens
when you use those expressions?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JayhawkJim]

Using... Sum(Abs([FieldName])) returns an error as follows; Cannot have
aggregate function in WHERE clause ([Referral Source].[HYF Courtesy
E]=Sum(Abs("HYF Courtesy E") AND... and it repeats the same info for each
field. The table is REFERRAL SOURCE and the first field that can be 'checked'
in the form under a category called 'Courtesy' is 'Excellent. I have
designeted that individual field in the db as [HYF Courtesy E].
--
Jayhawk Jim

"...without success... " doesn't give us many clues to go on. What happens
when you use those expressions?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have looked at, read, and tried everything on the Access Discussion Board
back to 2007 but I still can't get this query to return the correct info.
A
completed FORM allows users to 'check' a Data Control box to answer a
question with 5 possible degrees of satisfaction. It's the typical control
that enters a YES(-1) for TRUE and a NO(0) for FALSE in the Table. I have
entered 10 test responses into the table via the form successfully. In the
table, I can see that the cumulative sum of the colums are 6,2,2,1,0 for a
total of 11 records (5 fields) - which tells me that the data was entered
correctly. I want the query to display these totals (6,2,2,1,0) so I can
construct a chart of the results. Any help? In the Criteria line, I've
entered, Count(Abs([FieldName])) and Sum(Abs([FieldName])) without
success.

 

[Jeff Boyce]

Please post the full SQL statement you are trying to use.

By the way, using one field per question is how you might handle this if you
were working with a spreadsheet, but it is a maintenance nightmare when you
use Access.

Take a look at Duane H's AtYourSurvey to see an alternate way to handle
tests/surveys:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

Using... Sum(Abs([FieldName])) returns an error as follows; Cannot have
aggregate function in WHERE clause ([Referral Source].[HYF Courtesy
E]=Sum(Abs("HYF Courtesy E") AND... and it repeats the same info for each
field. The table is REFERRAL SOURCE and the first field that can be
'checked'
in the form under a category called 'Courtesy' is 'Excellent. I have
designeted that individual field in the db as [HYF Courtesy E].
--
Jayhawk Jim

"...without success... " doesn't give us many clues to go on. What
happens
when you use those expressions?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have looked at, read, and tried everything on the Access Discussion
Board
back to 2007 but I still can't get this query to return the correct
info.
A
completed FORM allows users to 'check' a Data Control box to answer a
question with 5 possible degrees of satisfaction. It's the typical
control
that enters a YES(-1) for TRUE and a NO(0) for FALSE in the Table. I
have
entered 10 test responses into the table via the form successfully. In
the
table, I can see that the cumulative sum of the colums are 6,2,2,1,0
for a
total of 11 records (5 fields) - which tells me that the data was
entered
correctly. I want the query to display these totals (6,2,2,1,0) so I
can
construct a chart of the results. Any help? In the Criteria line, I've
entered, Count(Abs([FieldName])) and Sum(Abs([FieldName])) without
success.

 

[John Spencer (MVP)]

You don't enter that expression in the where line. You enter the expression
in the FIELD line.

Field: Field1YesCount: Abs(Sum([FIeldName1]))

And since you are using aggregate functions you need to select View: Totals
from the menu and then in the new totals row change GROUP by to Expression
under this field.

From your limited description of the table set up, I think you will need to
do this five times - once for each field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[JayhawkJim]

John - Thank you - worked like a champ!
--
Jayhawk Jim

You don't enter that expression in the where line. You enter the expression
in the FIELD line.

Field: Field1YesCount: Abs(Sum([FIeldName1]))

And since you are using aggregate functions you need to select View: Totals
from the menu and then in the new totals row change GROUP by to Expression
under this field.

From your limited description of the table set up, I think you will need to
do this five times - once for each field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have looked at, read, and tried everything on the Access Discussion Board
back to 2007 but I still can't get this query to return the correct info. A
completed FORM allows users to 'check' a Data Control box to answer a
question with 5 possible degrees of satisfaction. It's the typical control
that enters a YES(-1) for TRUE and a NO(0) for FALSE in the Table. I have
entered 10 test responses into the table via the form successfully. In the
table, I can see that the cumulative sum of the colums are 6,2,2,1,0 for a
total of 11 records (5 fields) - which tells me that the data was entered
correctly. I want the query to display these totals (6,2,2,1,0) so I can
construct a chart of the results. Any help? In the Criteria line, I've
entered, Count(Abs([FieldName])) and Sum(Abs([FieldName])) without success.