Re: Query Too Complicated

Tech-Archive recommends: Fix windows errors by optimizing your registry

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
'====================================================


treysoul wrote:
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" wrote:

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

.

Relevant Pages

  • Re: Question about percents and grouping
    ... (SELECT SiteID, QuestionNum, Count(Response) ... If it is more like the first description, we can use a union query to normalize the data and then use the union query to build the query. ...
    (microsoft.public.access.queries)
  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)
  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)
  • Is yahoo being hijacked?
    ... I get no server response when query ... The server returned the following response headers: ... Connection: close ...
    (misc.invest.stocks)
  • Re: Is yahoo being hijacked?
    ... I get no server response when query ... > Connection: close ...
    (misc.invest.stocks)