Query Too Complicated

G

Guest

I have a survey that has 20 questions. The records have survey IDs. The
problem is i need to count the preferred responses in each field, then
determine the percent of preferred responses, and convert that to a readable
percentage (*100 then isolate using Left 4 characters.) Here is a copy of
one my expressions:

Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)

This expression over 20 fields is too much for Access to handle. I tried to
split it up over several queries, but with the first query which has just the
sum(iif((.....portion is too complicated...what can I do???
 
G

Guest

Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)

In this expression you have the puncuation all wrong.
You have IIf(([Did ... Why two parenthesis?

Why left four characters of the number?

Why not just update all the fields that have yes to 1?
 
G

Guest

KARL DEWEY said:
Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Click to expand...
Click to expand...
Rep Resolve Your Issue?]))*100),4)

In this expression you have the puncuation all wrong.
You have IIf(([Did ... Why two parenthesis?
Click to expand...

Not sure why I need two...but it somehow works for me.
Why left four characters of the number?
Click to expand...

(Be gentle...fore I am a beginner....lol)
After the calculation *100( .8534567) , i just want the 85.3
Why not just update all the fields that have yes to 1?
Click to expand...

I can, if the data is entered in a form, but I pulled the raw data from an
Excel sheet. But that sounds mor logical to convert that when I import the
data.
--
KARL DEWEY
Build a little - Test a little


treysoul said:
I have a survey that has 20 questions. The records have survey IDs. The
problem is i need to count the preferred responses in each field, then
determine the percent of preferred responses, and convert that to a readable
percentage (*100 then isolate using Left 4 characters.) Here is a copy of
one my expressions:

Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)

This expression over 20 fields is too much for Access to handle. I tried to
split it up over several queries, but with the first query which has just the
sum(iif((.....portion is too complicated...what can I do???
Click to expand...
Click to expand...
 
G

Guest

As a caviat to this, all of the data is not Yes or No....some is rank 1-10,
and one is "likely" "unlikely"...

KARL DEWEY said:
Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Click to expand...
Click to expand...
Rep Resolve Your Issue?]))*100),4)

In this expression you have the puncuation all wrong.
You have IIf(([Did ... Why two parenthesis?

Why left four characters of the number?

Why not just update all the fields that have yes to 1?
--
KARL DEWEY
Build a little - Test a little


treysoul said:
I have a survey that has 20 questions. The records have survey IDs. The
problem is i need to count the preferred responses in each field, then
determine the percent of preferred responses, and convert that to a readable
percentage (*100 then isolate using Left 4 characters.) Here is a copy of
one my expressions:

Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)

This expression over 20 fields is too much for Access to handle. I tried to
split it up over several queries, but with the first query which has just the
sum(iif((.....portion is too complicated...what can I do???
Click to expand...
Click to expand...
 
G

Guest

I attempted to remove the parenthesis and simplified the expression to
include just the sum, but it would not allow me to remove that second
parenthesis. I guess it's needed to isolate the field name. (just a guess)

another thought...is the complexity of the expression going to depend on the
number of records...because I'm doing this test on 40 surveys, which is only
about 5 days. The total month can be 200 or more records.

KARL DEWEY said:
Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Click to expand...
Click to expand...
Rep Resolve Your Issue?]))*100),4)

In this expression you have the puncuation all wrong.
You have IIf(([Did ... Why two parenthesis?

Why left four characters of the number?

Why not just update all the fields that have yes to 1?
--
KARL DEWEY
Build a little - Test a little


treysoul said:
I have a survey that has 20 questions. The records have survey IDs. The
problem is i need to count the preferred responses in each field, then
determine the percent of preferred responses, and convert that to a readable
percentage (*100 then isolate using Left 4 characters.) Here is a copy of
one my expressions:

Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)

This expression over 20 fields is too much for Access to handle. I tried to
split it up over several queries, but with the first query which has just the
sum(iif((.....portion is too complicated...what can I do???
Click to expand...
Click to expand...
 
J

John Spencer

You could try the following

Q02: Format(Sum(IIf([Did Rep Resolve Your Issue?])="Yes",1,0)) /
Count([Did Rep Resolve Your Issue?]) * 100,"#0.0")


Your real problem is that your structure is wrong. You really needed
someting more like a table with with the questionID, the responderID,
and the response. With that structure the query would be almost trivial.

Since is it probably to late to restructure your table, then you are
stuck with the above complicated query.

You might be able to fix this with a union query although twenty
questions may cause this to fail. For example with three questions you
could build a UNION query that looked like the following and save it.

Note that UNION queries cannot be built using the query grid, but must
be built in the SQL view.

SELECT "Q1" as QuestionID, [Question One] as Response
FROM YourTable
UNION ALL
SELECT "Q2" as QuestionID, [Question Two] as Response
FROM YourTable
UNION ALL
SELECT "Q3" as QuestionID, [Question Three] as Response
FROM YourTable


Save that as qNormal.

Now all you need is a query like the following to get the percentage
responses for all the questions and responses.

SELECT QuestionID
, Response
, Count(Response)/
(Select Count(Response)
FROM qNormal as T
WHERE T.QuestionID =qNormal.QuestionID)
FROM qNormal
GROUP BY QuestionID, Response



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

As a caviat to this, all of the data is not Yes or No....some is rank 1-10,
and one is "likely" "unlikely"...

KARL DEWEY said:
Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Click to expand...
Rep Resolve Your Issue?]))*100),4)
Click to expand...
Click to expand...
 
G

Guest

John,

It's not too late. I just started building this DB, so If I need to
restructure the data, I can. Can you or someone go into detail as to how the
survey will be structured?

I have a Question Table already, where each question has an ID Q01,
Q02...etc. But how do I match the responses, which some can be 1-10, Yes/No,
Likely/UNlikely, Acceptable/Unacceptable.

I have another table that has the "ResponderID", but it is an EmployeeID.
How would the responses be tied to the responder and the survey? By the way,
one employee may get more than one survey; sometimes 2 or 3 in a month.

Let me know what you come up with...thanks.

Trey

John Spencer said:
You could try the following

Q02: Format(Sum(IIf([Did Rep Resolve Your Issue?])="Yes",1,0)) /
Count([Did Rep Resolve Your Issue?]) * 100,"#0.0")


Your real problem is that your structure is wrong. You really needed
someting more like a table with with the questionID, the responderID,
and the response. With that structure the query would be almost trivial.

Since is it probably to late to restructure your table, then you are
stuck with the above complicated query.

You might be able to fix this with a union query although twenty
questions may cause this to fail. For example with three questions you
could build a UNION query that looked like the following and save it.

Note that UNION queries cannot be built using the query grid, but must
be built in the SQL view.

SELECT "Q1" as QuestionID, [Question One] as Response
FROM YourTable
UNION ALL
SELECT "Q2" as QuestionID, [Question Two] as Response
FROM YourTable
UNION ALL
SELECT "Q3" as QuestionID, [Question Three] as Response
FROM YourTable


Save that as qNormal.

Now all you need is a query like the following to get the percentage
responses for all the questions and responses.

SELECT QuestionID
, Response
, Count(Response)/
(Select Count(Response)
FROM qNormal as T
WHERE T.QuestionID =qNormal.QuestionID)
FROM qNormal
GROUP BY QuestionID, Response



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

As a caviat to this, all of the data is not Yes or No....some is rank 1-10,
and one is "likely" "unlikely"...

KARL DEWEY said:
Q02: Left(((Sum(IIf(([Did Rep Resolve Your Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)
Click to expand...
Click to expand...
Click to expand...
 
D

David Cox

([Did Rep Resolve Your Issue?])="Yes") resolves internally to -1 if true and
0 if false.

-avg([Did Rep Resolve Your Issue?])="Yes") will give you a value between 0
and 1 which can either be selected to be formatted as a percentage on output
or multiplied by 100 if you want to do it yourself.

The same thing will work for "likely".

Avg(ranking)*10 will give you a percentage answer, which will be wrong
because ranking should be defined from 0 to 10 for this task.

Avg(ranking-1)*11.1111 will give you a more accurate answer, but much more
confusing for your successors.

..


treysoul said:
As a caviat to this, all of the data is not Yes or No....some is rank
1-10,
and one is "likely" "unlikely"...

KARL DEWEY said:
Q02: Left(((Sum(IIf(([Did Rep Resolve Your
Issue?])="Yes",1,0))/Count([Did
Click to expand...
Rep Resolve Your Issue?]))*100),4)

In this expression you have the puncuation all wrong.
You have IIf(([Did ... Why two parenthesis?

Why left four characters of the number?

Why not just update all the fields that have yes to 1?
--
KARL DEWEY
Build a little - Test a little


treysoul said:
I have a survey that has 20 questions. The records have survey IDs.
The
problem is i need to count the preferred responses in each field, then
determine the percent of preferred responses, and convert that to a
readable
percentage (*100 then isolate using Left 4 characters.) Here is a copy
of
one my expressions:

Q02: Left(((Sum(IIf(([Did Rep Resolve Your
Issue?])="Yes",1,0))/Count([Did
Rep Resolve Your Issue?]))*100),4)

This expression over 20 fields is too much for Access to handle. I
tried to
split it up over several queries, but with the first query which has
just the
sum(iif((.....portion is too complicated...what can I do???
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to query survey responses from a table by question id 1
count() in a query 5
System Resource Exceeded/Query Too Complex 4
Left Join query not working in Access 2010 0
"Query too complex" workaround 4
Query Too Complex with Total line 8
Query running sum - help again 2
Query Exceeds the limit of data for a single record 7

Top